How to put dynamic select result in a variable

Question

0.00/5 (No votes)

See more:

i have the following select

SQL

SELECT OBJECTPROPERTY( OBJECT_ID(N'@TableName'), 'TableHasIdentity')

that needs tablename and tells us if a table has identity or not
i use this select in a stored procedure that has table name as input
and i want to put the result of select in int variable
so my stored procedure is

SQL

create procedure myprocedure 
@TableName nvarchar(255)
as
Begin
  Declare @result int 
  Exec('Set ' + @result +'=SELECT OBJECTPROPERTY( OBJECT_ID(N''' + @TableName + '''), ''' +'TableHasIdentity''' + ')''')
  print @result 
End

suppose the table has identity so the result must have the value one
but i don't get this value
how should i handle this?

Posted 20-Jan-15 23:29pm

oula alsheikh

Updated 20-Jan-15 23:32pm

Tomas Takac

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-01-21T00:02:00

Solution 1

Try using sp_executesql[^] instead of exec as it allows parameter definitions (both in and out)

Something like this might work (warning - I have not been able to test this)

SQL

Begin
Declare @result int
DECLARE @sSQL nvarchar(max)
DECLARE @ParmDefinition nvarchar(max)
SET sSQL = 'Set @resultOUT=SELECT OBJECTPROPERTY( OBJECT_ID(N''' + @TableName + '''), ''' +'TableHasIdentity''' + ')'''
SET @ParmDefinition = N'@resultOUT int OUTPUT';
EXEC sp_executesql @sSQL, @ParmDefinition, @resultOUT=@result OUTPUT;

Key thing to note is that the output parameter is not defined outside of the SQL - i.e. notice the difference between @result and @resultOUT

Posted 21-Jan-15 0:02am

CHill60

Comments

oula alsheikh 21-Jan-15 7:23am

i tried this but it doesn't work too @result has no value

CHill60 21-Jan-15 7:33am

Try @result=@resultOUT instead - as I warned, I wasn't able to test this. However the link I provided should give you enough information

oula alsheikh 22-Jan-15 5:50am

Did you mean the last line should have the following syntax
EXEC sp_executesql @sSQL, @ParmDefinition, @result=@resultOUT OUTPUT

itried this but i got the error
Must declare the scalar variable "@resultOUT".

can you test the code for me and give me the solution please

iam thinking to use cursor for select columns that has identity and test if tablename is one of them but that costs me to loop for all tables in every time i want to test a specific tablename i think that is not an ideal way to test if table has identity or not

CHill60 22-Jan-15 6:25am

It should have been right the first time, sorry. Check the link in the solution as it gives the correct references and an example.
I'm not in a position to test this at the moment.
One thing you can do is use PRINT sSQL to see what the sql statement is and just try running it - to make sure it is actually returning a result