Help with SELECT variable using SET

[avu]

Hello,

Can someone please tell me what'd be the correct syntax in using:

DECLARE @sqlStr varchar, @familyName varchar
SET @sqlStr = ('SELECT @familyName=LastName FROM someTable WHERE id=3')
EXEC(@sqlStr)

When running this, I get error: "Must declare the variable '@familyName'" (even though it's been declared above)

Thanks for your help.

 

[jbenson001]

YOu cannot declare multiple values on one line
you have to do

DECLARE @sqlStr varchar
declare @familyName varchar

 

[vongrunt]

EXECuted commands run in their own variable space, so @familyName is not visible from there.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[avu]

Thanks for the replies.

vongrunt: Can you suggest another method I can use to achieve this step (if any)?

 

[jbenson001]

You syntax is correct, my bad, vongrunt is 100% correct.

 

[JamesLean]

There is no need to use dynamic SQL for this query:

DECLARE @familyName varchar(20)

SELECT @familyName = LastName
FROM someTable
WHERE id = 3

Note I have specified a length for the varchar variable - if you don't do this it will default to 1, which I imagine won't be long enough to hold a family name ;-)

--James

 

[avu]

JamesLean: I've shortened the query to highlight the syntax problem that I have. The actual query is much longer and it carries dynamic criteria in the WHERE clause.

Thanks all again for your help.

 

[Catadmin]

avu,

I'm only mentioning this because no one else seemed to. Your Declare statement syntax is wrong.

DECLARE @sqlStr varchar, @familyName varchar

Should be:
DECLARE @sqlStr varchar(xx), @familyName varchar(xx)

The (xx) is the number of character bits you want your varchar datatype to be at max. You use this same syntax with Char, text, ntext and Nvarchar datatypes too. The difference with Char, text and Ntext is they are fixed to the # of character bits you put in. Varchar and Nvarchar are variable lengths with a maximum length of the character bits you put in.

Also, you can Declare multiple variables with one Declare statement. Most people tab them under each other for readability, but with the comma between them, there's no reason you can't put them on one line.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[avu]

Thanks Catadmin. I indeed had (xx) in all my char/text variables.

Here's a better description to my syntax problem:

CREATE PROCEDURE myProc (
   @input1 varchar(20),
   @input2 varchar(20)
)
AS
BEGIN
DECLARE @var1 varchar(20),
   @var2 varchar(20),
   @var3 int
DECLARE @sqlStr varchar(500)
SET @sqlStr = ('SELECT @var1=field1, @var2=field2, @var3=field3 
     FROM aTable1 
     WHERE (field4=''' + @input1 + ''') AND (field5=''' + @input2 + ''')')
EXEC(@sqlStr)

SET @sqlStr = ('INSERT INTO aTable2(someVal) VALUES (' + @var1 + ')')
EXEC(@sqlStr)
SET @sqlStr = ('UPDATE aTable3 SET field1=''' + @var2 + ''' 
     WHERE (id=' + @var3 + ')')
EXEC(@sqlStr)

END

Please don't pay too close attention to datatypes or how to create procedure syntax. My main issue is the syntax to acquire @var1, @var2, @var3 in the SELECT stmt and use them in the INSERT and UPDATE queries.

The error is "Must declare variable @var1". And it would stop there without further proceed to @var2 & @var3.

If the problem is as vongrunt described: "EXECuted commands run in their own variable space, so @... is not visible from there.", is there another way to script this?

Thanks much for your help.

 

[JamesLean]

When using variables in place of expressions, as in your example, then you don't need to use dynamic SQL. You can simply do:

CREATE PROCEDURE myProc
	@input1 varchar(20),
	@input2 varchar(20)
AS

DECLARE @var1 varchar(20),
	@var2 varchar(20),
	@var3 int

SELECT @var1 = field1, @var2 = field2, @var3 = field3
FROM aTable1
WHERE field4 = @input1
	AND field5 = @input2

INSERT aTable2 (someVal)
VALUES (@var1)

UPDATE aTable3
SET field1 = @var2
WHERE id = @var3

You only have to use dynamic SQL in places where variables are not allowed, eg in place of object names.

DECLARE @table sysname,
	@sql varchar(100)

SET @table = 'table1'

SET @sql = 'SELECT col1 FROM ' + @table
EXEC(@sql)

--James

 

[avu]

Thanks James.

Yes, your comment is absolutely right. I however used dynamic SQL because of a dynamic field to retrieve the data from.

My apology for not fully describing the script. I ended up using cursor to solve the problem. Here's part of the solution:

SET @sqlStr = 'DECLARE cursor_uProgram CURSOR FOR 
   SELECT uDB.uName, uProgram.pName, uProgram.' + @pGroup + ' FROM uProgram 
   INNER JOIN uDB ON uProgram.uId = uDB.uId 
   WHERE (uProgram.progDays LIKE ''%' + CAST(@progDay AS varchar) + '%'') 
   AND (uDB.Active = ''yes'')'
EXEC(@sqlStr)
OPEN cursor_uProgram
FETCH NEXT FROM cursor_uProgram INTO @uName, @pName, @pGroup
....

Thanks.