execute dinamic query

Posted on 2011-02-14
Last Modified: 2012-05-11
Im trying to get a value from a table passed as argument a long with its ID

SET @ClientID = '(SELECT ClientID FROM ' + @tablename + ' WHERE ID = ' + @JobID + ')'

Any ideas how i can retrieve the clientID in the example above?
Question by:arcross
  • 2
LVL 11

Expert Comment

ID: 34887380
LVL 11

Expert Comment

ID: 34887433
DECLARE @sql nvarchar(500)
SET @sql = N'SELECT @ClientId = ClientID FROM ' + @tablename + ' WHERE ID = ' + @JobID
EXEC sp_executesql @sql

Author Comment

ID: 34887483
this is what i get

Must declare the scalar variable "@ClientID".
LVL 23

Accepted Solution

Rajkumar Gs earned 500 total points
ID: 34887954
This should be handled as the way demonstrated here

I think, it would be like this
DECLARE @sql nvarchar(500)
SET @ParmDefinition = N'@ClientId int'
SET @sql = N'SELECT @ClientIdOUT = ClientID FROM ' + @tablename + ' WHERE ID = ' + @JobID
EXEC sp_executesql @sql, @ParmDefinition, @ClientIdOUT=@ClientId OUTPUT
SELECT @ClientId

Open in new window

If any error please refer that link's instructions


Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Update Query - What's wrong with this. 18 20
affinity mask in sql server 1 23
xpath sql query 2008 8 44
Sql query 107 30
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sending a Secure fax is easy with eFax Corporate ( First, just open a new email message. In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 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

27 Experts available now in Live!

Get 1:1 Help Now