June 3, 2003 at 1:21 am
Hi!
Tried to find this in BOL, but could not...
How can I fetch a single column [name] from the following cursor to a variable @user_name?
DECLARE @user_name varchar(50)
SET @user_name = 'sa'
DECLARE user_srvroles CURSOR
LOCAL
SCROLL
STATIC
READ_ONLY
FOR
SELECT [name], [dbname], [language], [isntuser],
[sysadmin], [securityadmin], [serveradmin], [setupadmin], [processadmin],
[diskadmin], [dbcreator], [bulkadmin], [loginname]
FROM [master].[dbo].[syslogins]
where [name] = @user_name
OPEN user_srvroles
FETCH NEXT FROM user_srvroles ??? @name ???? INTO @user_name
IF @@FETCH_STATUS <> 0
PRINT 'No such user'
ELSE
PRINT @user_name
CLOSE user_srvroles
DEALLOCATE user_srvroles
June 3, 2003 at 1:55 am
When you declare a cursor, you must fetch all the columns into variables - you cannot fetch some of the variables.
There are a couple of other ways of doing what you need:
1. After you open the cursor, check the value of @@cursor_rows. If it is 1 then there is a row in the table for the user, if it is 0 then there is no row. Just be aware that some cursors are dynamic and that the value of @@cursor_rows is -1. Off the top of my head I think you will be OK with a STATIC cursor but check BOL if you have problems.
2. Count the number of rows and put the value into a variable and check that:
DECLARE @user_name varchar(50), @count_user int
SET @user_name = 'sa'
set @count_user = 0
select @count_user = count(*)
from [master].[dbo].[syslogins]
where [name] = @user_name
if @count_user = 0 print 'No such user'
else print @user_name
There may be a system stored procedure to do this which might be even easier.
Jeremy
June 3, 2003 at 2:46 am
Suppose I use a stored procedure. How can place data (few records) it returns into a cursor or a temporary table. I need something like:
select * from [exec sp_helplogins @user_name]
June 4, 2003 at 6:38 am
quote:
How can I fetch a single column [name] from the following cursor to a variable @user_name?
You need to declare variables for everything you are fetching. For example,
Declare
@name sysname,
@dbname nvarchar(128),
@language nvarchar(128),
etc...
Then your fetch statement needs to be:
FETCH NEXT FROM user_srvroles
INTO @name, @dbname, @language, etc...
You don't need to reference @user_name in the FETCH NEXT statement. You used it as a filter when you declared the cursor.
For that matter, why even use a cursor? You are only fetching a single row worth of data. I would simply do this...
SELECT @name = name,
@dbname = dbname,
@language = language
etc...
FROM syslogins
WHERE name = @user_name
You'll still need to declare a variable for every field you need.
June 4, 2003 at 6:50 am
I just wanted to fetch a single column at one place of the procedure and take the others at another. So "partial" fetching is not possible.
Thanks all for help
June 4, 2003 at 9:00 am
Can you say
Select * into #MyTempTable
Exec MyProcName @Param1, @Param2....
paul
paul
June 5, 2003 at 6:55 am
If the variables are going to be used later in the procedure anyway, then what is the harm in fetching all variables in one place and use those where ever you want later on?
Edited by - vijaygill on 06/05/2003 06:56:11 AM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply