January 5, 2004 at 12:21 pm
I have a stored procedure in SQL server 2000 that has a statement like this
SET @mySQL = 'SELECT sum ( CAST (file_size_bytes as bigint))/1024 as image_size, count( Image_Filename) as tif_total from ' + @filename +
' WHERE Image_Filename LIKE ''%tif'' AND Daily_Date_Transferred BETWEEN '' ' + @beg_date + ''' and ''' + @end_date + ''''
EXEC @mySQL
This has to be a dynamic SQL statement since it depends on variables obtained from the previous fetch .
The problem that I have is I need to assign variables to the 2 values that I get from the SQL statement and use them later on. Whenever I use the = and assign a variable I get errors.
January 5, 2004 at 2:30 pm
When returning variables from dynamic SQL, it is easier and cleaner to use the sp_executeSQL stored proc.
Declare @mySQL nvarchar(500)
Declare @myParms nvarchar(100)
Declare @var1 int, @var2 int
SET @mySQL = N'SELECT @sz = sum ( CAST (file_size_bytes as bigint))/1024 as image_size, @CT = count( Image_Filename) as tif_total from ' + @filename +
' WHERE Image_Filename LIKE ''%tif'' AND Daily_Date_Transferred BETWEEN '' ' + @beg_date + ''' and ''' + @end_date + ''''
Set @myParms = N'@Sz int output, @CT int output'
EXEC sp_executesql @mySQL, @myParms, @sz = @var1, @CT = @var2
-- Var1 and Var2 now contain the values generated by the SQL...
January 6, 2004 at 7:09 am
Thanku for your response. When I tried sp_executesql just as you have mentioned above, I get the following error:
Server: Msg 141, Level 15, State 1, Line 1
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
I would appreciate any help on this.
This method would save me a lot of processing involved with putting this information in a temp table and manipulating that date to get the results that I want.
January 6, 2004 at 9:46 am
Try a slight modification of brendthess's suggestion:
Declare @mySQL nvarchar(500)
Declare @myParms nvarchar(100)
SET @mySQL = N'SELECT @sz = sum ( CAST (file_size_bytes as bigint))/1024 as image_size, @CT = count( Image_Filename) as tif_total from ' + @filename +
' WHERE Image_Filename LIKE ''%tif'' AND Daily_Date_Transferred BETWEEN '' ' + @beg_date + ''' and ''' + @end_date + ''''
Set @myParms = N'@Sz int output, @CT int output'
EXEC sp_executesql @mySQL, @myParms, @sz = @sz output , @CT = @CT output
-- @sz and @CT now contain the values generated by the SQL...
Note the 'output' parameter in the sp_executesql statement. Also the @sz and @CT parameters are defined outside the scope of the Set @mySQL statement. There are actually 2 versions of @sz and @CT. brendthess tried to make this clear by using @var1 and @var2 but the variables were not defined correctly. I find it easier sometimes to give them the same name, but you can experiment with this if you want to use @var1 and @var2 outside the scope of the dynamic SQL.
Francis
January 6, 2004 at 10:59 am
thanks to both of you . defining the variables as OUTPUT makes it work now !
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply