September 10, 2003 at 1:41 pm
I want to get the result of a following query to a stored procedure variable. How do i get it.
Query:
Select Count(*) from pubs.dbo.authors
SP:
Declare @tablecount int
exec @tablecount = 'Select count(*) from authors'
does not work. Thanks in advance.
September 10, 2003 at 1:46 pm
In the stored procedure write:
declare @tablecount int
select @tablecount = count(*) from authors
-Ken
September 10, 2003 at 1:49 pm
DECLARE @tablecount
set @tablecount = (Select count(*) from authors)
--to see the results in Sql Query analyzer:
SELECT @tablecount
It's the parentesis that makes it work...
September 10, 2003 at 1:51 pm
I can do that. Except that the tablename also comes from another variable. So, I have to use a dynamic sql.
Declare @tablename sysname
set @tablename = 'authors'
set @strsql = 'select count(*) from ' + @tablename
exec (@strsql) -- result of this exec stmt needs to be assigned to @tablecount
September 10, 2003 at 2:23 pm
Uh, how about a dynamic stored procedure. Like this:
Declare @tablename sysname
declare @tablecount int
declare @strsql varchar(8000)
set @tablename = 'authors'
select @strsql = 'create procedure #temptest (@variable int = null OUTPUT) as select @variable = count(*) from ' + @tablename
exec (@strsql)
exec #temptest @variable = @tablecount OUTPUT
select @tablecount
I'm quite impressed with how this works, I always wanted to play with temp procs but never had the reason to.
-Ken
September 10, 2003 at 2:52 pm
Interesting way to do that with a temporary SP. Here is another way using sp_executesql:
Declare @tablename sysname
declare @tablecount int
declare @strsql nvarchar(4000)
set @tablename = 'pubs.dbo.authors'
set @strsql = 'select @tablecount=count(*) from ' + @tablename
exec sp_executesql @strsql,N'@tablecount int out',@tablecount out
print @tablecount
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 10, 2003 at 4:10 pm
Interesting, how SQL works unlike traditional languages. Thanks Guys. Thanks for your help.
September 10, 2003 at 10:23 pm
Getting a query result stored in a vaiable is pretty simple as you issue a select statement to get result. However assigning and retrieving could not be combined in a one single SQL Statement.
Here it goes...
Declare @my_var int
SELECT @my_var = count(*)
FROM [sysobjects]
WHERE [type] = N'U'
SELECT @my_var
The above piece of code will display the number of tables present in a database.
Regards
SL Narayan
Best Reg@rds
Narayan
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply