If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Query in Stored procedure

Hi,
See if u could help me with the following...
How I can retrieve value returned by Query into a variable...

e.g If I want name of Customer in a variable whose account no is 5

I have written
set @Name=select Name from Customers where AccountNo=5
It doesn't work it gives error...
Also I want to do this stored proc. bcoz futher I need tht variable @Name for some other Query...
Waiting for some ans.

I want to use it in stored proc.
where I am trying to pass tablename as a parameter..
Wht I am doing is:
select @Name=('select Name from '+@TableName+'where Accid = '+@AccId)
It doesn't return the value of @Name but it returns 'select Name'

That's a totally different problem to your initial one. Is there any more to this? Do you seriously have loads of tables where you store account details? This is very likely a design error. You should also look up SQL Injection - you are opening up a serious security vulnerability.

The EXEC code executes in a different scope to the rest of the procedure. This is not trivial to do. It is also a Big Red Flag that this is not something you should blindly be doing as, to repeat myself, this smacks of poor design and poorer practice.

Look up sp_executesql - you can use that to return the value of parameters back to your calling code.

SET @SQLSTRING=N'SELECT @NAMEOUT=NAME FROM @TABLENAME WHERE AccID=@AccID'
SET @PARMDEFINITION=N'@TABLENAME VARCHAR(30),@AccID INT,
@NAMEOUT VARCHAR(30) OUTPUT'
SET @INTVARIABLE=1
SET @XYZ='CustomerMASTER'

SET @SQLSTRING=N'SELECT @NAMEOUT=NAME FROM @TABLENAME WHERE AccID=@AccID'
SET @PARMDEFINITION=N'@TABLENAME VARCHAR(30),@AccID INT,
@NAMEOUT VARCHAR(30) OUTPUT'
SET @INTVARIABLE=1
SET @XYZ='CustomerMASTER'