If you've ever tried to develop a stored procedure, pass a table as a parameter into the procedure, and populate...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.