passing local variable in select 1

[cfdave]

I am trying to update a number of rows in a table. I am retrieving the number of rows with a query and assigning a local variable. Here is the code segment.

select top 1 @MS= UPSMaxSize from SorterChuteTemp where chutestatus = 'SUS' order by chuteID
if(@MS > @Size) -- will fit
begin
-- assign the chutes
UPDATE sorterchutetemp
SET chutestatus = 'ACT'
FROM (SELECT TOP @ms * FROM sorterchutetemp ORDER BY chuteid
WHERE chutestatus = 'SUS'
end
else -- won't fit
begin
-- generate message
end


I'm getting an error message on the FROM (SELECT line.

Can anyone help?


Thanks in advance!

Dave

 

[jbenson001]

You can't use variables in a TOP statement.

 

[cfdave]

Any suggestions as to how I can accomplish this?

 

[jbenson001]

The only way I know of would be to use dynamic SQL.

 

[ousoonerjoe]

[COLOR=blue]DECLARE[/color] @Sql [COLOR=blue]VARCHAR[/color](2000)
[COLOR=blue]SET[/color] @Sql = ([COLOR=red]'SELECT TOP '[/color] + @ms +  [COLOR=red]'* FROM sorterchutetemp ORDER BY chuteid'[/color])
[COLOR=blue]EXEC[/color] @Sql;

Is one example of how to get there, But you MUST be careful when using dynamic SQL so as not to allow for hacks.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[bborissov]

You should use so called Dynamic SQL:
BTW the whole update clause is not right:
You didn't close parenthesis for the derived table.
You didn't alias that derived table.
You didn't JOIN derived table with the main one.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[cfdave]

Thanks! works fine.

 

[cfdave]

Borislav,

How would you write the dynamic SQL to do this?

 

[bborissov]

Dynamic SQL is what ousoonerjoe used in his/her example.
Create a string variable with whole query in it and then use EXEC() function or sp_executesql Stored Procedure to execute it.
But keep in mind that Dynamic SQL is a perdormance killer and also a way to get injection attacks.
So use them only when you have no other choice.

BTW ousoonerjoe will bomb just because @ms is an integer and integers can't be concatenated to strings. You must use CAST() or CONVERT() to convert that variable to varchar().

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ousoonerjoe]

Yeah... Bborissov is correct. Only use Dynamic SQL when you have to.

My example above will also fail, due to not putting a space before the astrix. to be a bit more precise here is a little more detail on how to approach it using dynamic SQL. This is untested for the most part so minor adjustments may be required (NOTE: I did not fix the actual UPDATE statement as Borissov suggested, this example is just for showing you how to go about the Dynamic SQL.):

[COLOR=blue]DECLARE[/color] @MS		[COLOR=blue]INT[/color]
[COLOR=blue]DECLARE[/color] @Size	[COLOR=blue]INT[/color]
[COLOR=blue]DECLARE[/color] @Sql	[COLOR=blue]VARCHAR[/color](1000)

[COLOR=blue]SET[/color] @MS = ([COLOR=blue]SELECT TOP[/color] 1 UPSMaxSize [COLOR=blue]FROM[/color] SorterChuteTemp [COLOR=blue]WHERE[/color] ChuteStatus = [COLOR=red]'SUS'[/color] [COLOR=blue]ORDER BY [/color] ChuteID);

[COLOR=blue]IF[/color](@MS > @Size)   [COLOR=green] -- will fit[/color]
    [COLOR=blue]BEGIN[/color]
        [COLOR=green]-- assign the chutes[/color]
		[COLOR=blue]SET[/color] @Sql = ([COLOR=red]'UPDATE SorterChuteTemp
			SET ChuteStatus = ''ACT''
			FROM (SELECT TOP '[/color] + [COLOR=fuchsia]CAST[/color](@MS + [COLOR=red]' * FROM SorterChuteTemp ORDER BY Chuteid)
			WHERE  ChuteStatus = ''SUS'''[/color]);
		[COLOR=blue]EXEC[/color] (@Sql);
    [COLOR=blue]END
ELSE[/color]           [COLOR=green] -- won't fit[/color]
    [COLOR=blue]BEGIN[/color]
        [COLOR=green]-- generate message[/color]
    [COLOR=blue]END[/color]

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[ousoonerjoe]

Yeah... Bborissov is correct. Only use Dynamic SQL when you have to.

My example above will also fail, due to not putting a space before the astrix. to be a bit more precise here is a little more detail on how to approach it using dynamic SQL. This is untested for the most part so minor adjustments may be required (NOTE: I did not fix the actual UPDATE statement as Borissov suggested, this example is just for showing you how to go about the Dynamic SQL.):

[COLOR=blue]DECLARE[/color] @MS		[COLOR=blue]INT[/color]
[COLOR=blue]DECLARE[/color] @Size	[COLOR=blue]INT[/color]
[COLOR=blue]DECLARE[/color] @Sql	[COLOR=blue]VARCHAR[/color](1000)

[COLOR=blue]SET[/color] @MS = ([COLOR=blue]SELECT TOP[/color] 1 UPSMaxSize [COLOR=blue]FROM[/color] SorterChuteTemp [COLOR=blue]WHERE[/color] ChuteStatus = [COLOR=red]'SUS'[/color] [COLOR=blue]ORDER BY [/color] ChuteID);

[COLOR=blue]IF[/color](@MS > @Size)   [COLOR=green] -- will fit[/color]
    [COLOR=blue]BEGIN[/color]
        [COLOR=green]-- assign the chutes[/color]
		[COLOR=blue]SET[/color] @Sql = ([COLOR=red]'UPDATE SorterChuteTemp
			SET ChuteStatus = ''ACT''
			FROM (SELECT TOP '[/color] + [COLOR=fuchsia]CAST[/color](@MS [COLOR=blue]AS VARCHAR[/color](10)) + [COLOR=red]' * FROM SorterChuteTemp ORDER BY Chuteid)
			WHERE  ChuteStatus = ''SUS'''[/color]);
		[COLOR=blue]EXEC[/color] (@Sql);
    [COLOR=blue]END
ELSE[/color]           [COLOR=green] -- won't fit[/color]
    [COLOR=blue]BEGIN[/color]
        [COLOR=green]-- generate message[/color]
    [COLOR=blue]END[/color]

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[cfdave]

Thank you all for all your help. I believe I can make this work from here.