Solved

T-SQL sql server 2005 execute command by passing a  variable

Posted on 2010-09-21
3
301 Views
Last Modified: 2012-05-10
I would like to execute simple commands such as the following:

delete @tablename
insert into @tablename .......

where @tablename is a varchar  parameter I pass to the stored procedure.

How can i do this
0
Comment
Question by:adimit
3 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33729738
You need to generate dynamic SQL Statements.

Mandatory BIG warning : do read up on "SQL Injection" (search on Google) before you proceed if you are using dynamic SQL with user input, but it does not look like the case here.

Something like this:

create proc mySP
@tablename sysname
as
declare @sql nvarchar(max)
set @sql = 'delete ' + @tablename -- everything
exec (@sql)
set @sql = 'insert into ' + @tablename + ' (col) values (val)'
exec (@sql)
0
 
LVL 4

Expert Comment

by:timexist
ID: 33729855
0
 

Author Closing Comment

by:adimit
ID: 33729918
thank you, I was missing the parentheses around the variable name in the exec command
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now