If you've ever tried to develop a stored procedure, pass a table as a parameter into the procedure, and populate a variable through a variable from that table, you'll typically get a syntax error or a message that you need to declare the variable, even if you already have.
The following code involves writing the value to a temporary table and then writing the value of the table to the variable. This may not be new, but I've found it to be helpful in certain situations:
create procedure spProp @Table varchar(25) as SET NOCOUNT ON declare @Count int, @Cmd varchar(255) create table #Temp (holder int null) select @Cmd = 'insert into #Temp (holder) select count(author) from ' + @Table + ' where authid = 1 exec (@Cmd) set @Count = (select holder from #Temp) truncate table #Temp print 'Value is: ' + convert(varchar(10), @Count) SET NOCOUNT OFF go
For More Information
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL gurus are waiting to answer your technical questions.