| Groups | Blog | Home |
sql server programming : Stored Procedure help.
I've written this stored procedure in SQL 2000. When I run it, I get an error that I've not declared a variable. Full error is: Inventory: Counting history, ID = 151 Server: Msg 137, Level 15, State 2, Line 1 Must declare the variable '@idHistory'. (I get this line for each table) this is my stored procedure code - I'm new to this so apologies for any dodgy code :) CREATE PROCEDURE [dbo].[InventoryCountFromidHistory] @idHistory INT AS IF (@IDHistory IS NULL) RETURN -- No target, we can do nothing -- Drop a message in the Windows Application log DECLARE @msg nvarchar(1024) SET @msg = 'Inventory: Counting history, ID = ' + STR(@IDHistory) PRINT @msg EXEC sp_MSforeachtable "Select Count(idHistory)as [?] from ? Where idHistory=@idHistory" GO Can anyone help me out, I'm probably doing something totally stupid :) thanks
There are a few issues with this sproc.
1. sp_msforeachtable will force the system to step through every user table in the database and do a count on IdHistory column. Are you sure that every table in your current database has this column. 2. @idHistory is available outside of sp_msforeachtable scope. Thus, the error you're getting. The workaround for #2 is: CREATE PROCEDURE [dbo].[InventoryCountFromidHistory] @idHistory INT AS IF (@IDHistory IS NULL) RETURN -- No target, we can do nothing -- Drop a message in the Windows Application log DECLARE @msg nvarchar(1024) SET @msg = 'Inventory: Counting history, ID = ' + STR(@IDHistory) PRINT @msg declare @sql sysname set @sql='Select Count(idHistory)as [?] from ? Where idHistory='+cast(@idHistory as nvarchar) EXEC sp_MSforeachtable @sql GO [quoted text, click to view] "Andrew Robinson" <cyn3rgy@hotmail.com> wrote in message news:2e41d4e0.0412082138.5b115bd8@posting.google.com... > Hi All > > I've written this stored procedure in SQL 2000. When I run it, I get > an error that I've not declared a variable. Full error is: > > Inventory: Counting history, ID = 151 > Server: Msg 137, Level 15, State 2, Line 1 > Must declare the variable '@idHistory'. (I get this line for each > table) > > > this is my stored procedure code - I'm new to this so apologies for > any dodgy code :) > > CREATE PROCEDURE [dbo].[InventoryCountFromidHistory] @idHistory INT > AS > IF (@IDHistory IS NULL) > RETURN -- No target, we can do nothing > > -- Drop a message in the Windows Application log > DECLARE @msg nvarchar(1024) > SET @msg = 'Inventory: Counting history, ID = ' + STR(@IDHistory) > PRINT @msg > > EXEC sp_MSforeachtable "Select Count(idHistory)as [?] from ? Where > idHistory=@idHistory" > GO > > Can anyone help me out, I'm probably doing something totally stupid :) > > thanks > > Andrew
[quoted text, click to view]
Andrew Robinson wrote: > Hi All > > I've written this stored procedure in SQL 2000. When I run it, I get > an error that I've not declared a variable. Full error is: > > Inventory: Counting history, ID = 151 > Server: Msg 137, Level 15, State 2, Line 1 > Must declare the variable '@idHistory'. (I get this line for each > table) > > > this is my stored procedure code - I'm new to this so apologies for > any dodgy code :) > > CREATE PROCEDURE [dbo].[InventoryCountFromidHistory] @idHistory INT > AS > IF (@IDHistory IS NULL) > RETURN -- No target, we can do nothing > > -- Drop a message in the Windows Application log > DECLARE @msg nvarchar(1024) > SET @msg = 'Inventory: Counting history, ID = ' + STR(@IDHistory) > PRINT @msg > > EXEC sp_MSforeachtable "Select Count(idHistory)as [?] from ? Where > idHistory=@idHistory" > GO > > Can anyone help me out, I'm probably doing something totally stupid :) > > thanks > > Andrew sp_MSForEachTable is an undocumented function. You can use it, but just beware it could disappear or change in a future release. I'm guessing the sp_MS* function is executing dynamic SQL on each table in the current database. You r dynamic SQL is referencing a local variable. Try building the string using STR(@IDHistory) like you did for the message. I'm also not a big fan of Return statements in the middle of the code. Better to have: IF (@IDHistory IS NOT NULL) Begin -- stuff End Return 0 -- David Gugick Imceda Software www.imceda.com
Andrew
You are confused. This undocumented stored procedure does not return OUTPUT parameter You will probably want to look at below example from the BOL CREATE PROCEDURE get_sales_for_title @title varchar(80), -- This is the input parameter. @ytd_sales int OUTPUT -- This is the output parameter. AS -- Get the sales for the specified title and -- assign it to the output parameter. SELECT @ytd_sales = ytd_sales FROM titles WHERE title = @title RETURN GO The following program executes the stored procedure with a value for the input parameter and saves the output value of the stored procedure in the @ytd_sales_for_title variable local to the calling program. -- Declare the variable to receive the output value of the procedure. DECLARE @ytd_sales_for_title int -- Execute the procedure with a title_id value -- and save the output value in a variable. EXECUTE get_sales_for_title "Sushi, Anyone?", @ytd_sales = @ytd_sales_for_title OUTPUT -- Display the value returned by the procedure. PRINT 'Sales for "Sushi, Anyone?": ' + convert(varchar(6),@ytd_sales_for_title) GO Sales for "Sushi, Anyone?": 4095 [quoted text, click to view] "Andrew Robinson" <cyn3rgy@hotmail.com> wrote in message news:2e41d4e0.0412082138.5b115bd8@posting.google.com... > Hi All > > I've written this stored procedure in SQL 2000. When I run it, I get > an error that I've not declared a variable. Full error is: > > Inventory: Counting history, ID = 151 > Server: Msg 137, Level 15, State 2, Line 1 > Must declare the variable '@idHistory'. (I get this line for each > table) > > > this is my stored procedure code - I'm new to this so apologies for > any dodgy code :) > > CREATE PROCEDURE [dbo].[InventoryCountFromidHistory] @idHistory INT > AS > IF (@IDHistory IS NULL) > RETURN -- No target, we can do nothing > > -- Drop a message in the Windows Application log > DECLARE @msg nvarchar(1024) > SET @msg = 'Inventory: Counting history, ID = ' + STR(@IDHistory) > PRINT @msg > > EXEC sp_MSforeachtable "Select Count(idHistory)as [?] from ? Where > idHistory=@idHistory" > GO > > Can anyone help me out, I'm probably doing something totally stupid :) > > thanks > > Andrew
Thanks oj, that worked a treat. I was hoping a stored procedure would
be faster than doing the idHistory count from the client side (nested select statemetns in an asp script). It doesn't seem to be. I might have to look at redesigning the db I think. Appreciate the reply :) thanks to everyone else for the feedback. andrew [quoted text, click to view] oj wrote:
> There are a few issues with this sproc. > 1. sp_msforeachtable will force the system to step through every user table > in the database and do a count on IdHistory column. Are you sure that every > table in your current database has this column. > 2. @idHistory is available outside of sp_msforeachtable scope. Thus, the > error you're getting. > > The workaround for #2 is: > > CREATE PROCEDURE [dbo].[InventoryCountFromidHistory] @idHistory INT > AS > IF (@IDHistory IS NULL) > RETURN -- No target, we can do nothing > > -- Drop a message in the Windows Application log > DECLARE @msg nvarchar(1024) > SET @msg = 'Inventory: Counting history, ID = ' + STR(@IDHistory) > PRINT @msg > > declare @sql sysname > set @sql='Select Count(idHistory)as [?] from ? Where > idHistory='+cast(@idHistory as nvarchar) > > EXEC sp_MSforeachtable @sql > > GO > > "Andrew Robinson" <cyn3rgy@hotmail.com> wrote in message > news:2e41d4e0.0412082138.5b115bd8@posting.google.com... > > Hi All > > > > I've written this stored procedure in SQL 2000. When I run it, I get > > an error that I've not declared a variable. Full error is: > > > > Inventory: Counting history, ID = 151 > > Server: Msg 137, Level 15, State 2, Line 1 > > Must declare the variable '@idHistory'. (I get this line for each > > table) > > > > > > this is my stored procedure code - I'm new to this so apologies for > > any dodgy code :) > > > > CREATE PROCEDURE [dbo].[InventoryCountFromidHistory] @idHistory INT > > AS > > IF (@IDHistory IS NULL) > > RETURN -- No target, we can do nothing > > > > -- Drop a message in the Windows Application log > > DECLARE @msg nvarchar(1024) > > SET @msg = 'Inventory: Counting history, ID = ' + STR(@IDHistory) > > PRINT @msg > > > > EXEC sp_MSforeachtable "Select Count(idHistory)as [?] from ? Where > > idHistory=@idHistory" > > GO > > > > Can anyone help me out, I'm probably doing something totally stupid :) > > > > thanks > > > > Andrew
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |