Dynamic Query Question

Kevin Marois

When I run this:

DECLARE @voter_id INT
SET @Command = 'SELECT @voter_id = voterid FROM tblCamp_CT WHERE ' + @Query
EXEC (@Command)

I get the error

Must declare the scalar variable "@voter_id".

Why is this happening????

Everything makes sense in someone's mind

Blue_Boy

You must include DECLARE @voter_id INT inside single quotes. e.g

SET @Command = 'DECLARE @voter_id INT SELECT @voter_id = voterid FROM tblCamp_CT WHERE ' + @Query
EXEC (@Command)

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.cacttus.com

smcnulty2000

Blue Boy is correct. The 'why' of it is that the context has shifted. The exec runs inside of it's own little world that has no idea of what occurred outside of the @command string. You can see a similar effect whenever you hit a 'go' statement. Variables become undeclared because the context has changed. The difference is that when you run an exec it doesn't break the context outside of the exec the way 'go' does.

_____________________________ Give a man a mug, he drinks for a day. Teach a man to mug...

Kevin Marois

But if I declare it in the @Command string, I cannot use @voter_id outside the string, correct? I still get the same error message because @voter_id only exists in the string.

Everything makes sense in someone's mind

dasblinkenlight

Others have answered the 'why' question, so let's go straight to the 'how' part. As clever as it may seem, your trick does not work, because SQL server does not let you parameterize dynamic SQL. However, you can trick it by observing that dynamic SQL is essentially an anonymous stored procedure; once you give it a name, you can start passing parameters to it.

smcnulty2000

Absolutely. Dasblinkenlight's solution is a nice one to the problem, BTW. I normally build a temp table and just run the data into that. Using an insert or an update. I know it seems (both suggested solutions) like a lot of overhead to get one piece of data out but that's really what it takes.

create table #Value (
val int
)

set @command = 'insert into #value (val) SELECT top 1 voterid FROM tblCamp_CT WHERE ' +@Query

exec(@command)

set @voter_id=(select top 1 val from #Value)
delete #Value

Like that. You could also research sp_executesql for your project. this works, for example:

declare @voter_id int , @query varchar(max), @command nvarchar(4000)
,@parm nvarchar(20)

set @query='1=1'

SET @Command = 'SELECT top 1 @voter_id = voterid FROM tblCamp_CT WHERE ' + @Query

set @parm ='@voter_id int output '

exec sp_executesql @command,@parm ,@voter_id out

select @voter_id test

As you can see the sp_executesql gives you another option. Some of this depends on what your personal flavor is toward a given solution.

_____________________________ Give a man a mug, he drinks for a day. Teach a man to mug...

PIEBALDconsult

That would be another reason not to use stored procedures. Things like that are so much simpler when the SQL is in your DAL.

Mycroft Holmes

PIEBALDconsult wrote:

That would be another reason not to use stored procedures

Boo, hissss, brrrppp and other sundry rude noises. Oh wait we've been round this tree before.

Never underestimate the power of human stupidity RAH

S Douglas

PIEBALDconsult wrote:

That would be another reason not to use stored procedures. Things like that are so much simpler when the SQL is in your DAL.

I'll bite, this is easy in a proc. Seems like it would be way more difficult embedded in the DAL. I'm interested in how you would accomplish it?

EXEC sp_executesql @DYNA_SQL, N'@NUM_ROWS int out'

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

PIEBALDconsult

What does it do? :confused:

S Douglas

Pretty much the same thing that smcnulty2000 suggested doing in the second suggestion. sp_executesql allows for parameters to be fed in / out the dynamic SQL being executed. http://msdn.microsoft.com/en-us/library/ms188001.aspx[^]

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

PIEBALDconsult

Here are a couple of simple examples. Not using my usual data access classes. I prefer the ExecuteScalar, it was designed for this sort of thing.

        System.Data.IDbConnection dbc = new System.Data.SqlClient.SqlConnection
        (
            @"Server='localhost\\SQLEXPRESS'; Database='Rubbish'; Trusted\_Connection='True'"
        ) ;

        System.Data.IDbCommand cmd = dbc.CreateCommand() ;

        cmd.CommandText = "SELECT @voter\_id=Id FROM Account WHERE " + "Name = 'Two'" ;

        System.Data.IDbDataParameter prm = cmd.CreateParameter() ;

        prm.ParameterName = "@voter\_id" ;
        prm.DbType = System.Data.DbType.Int32 ;
        prm.Direction = System.Data.ParameterDirection.Output ;

        cmd.Parameters.Add ( prm ) ;

        dbc.Open() ;

        cmd.ExecuteNonQuery() ;

        int id = (int) prm.Value ;

        cmd.Parameters.Clear() ;
        cmd.CommandText = "SELECT Id FROM Account WHERE " + "Name = 'Three'" ;

        object o = cmd.ExecuteScalar() ;

        if ( o != System.DBNull.Value )
        {
            id = (int) o ;
        }

        dbc.Close() ;

PIEBALDconsult

Here's another take on it:

    public static T
    GetAccountId<T>
    (
        this System.Data.IDbConnection dbc
    ,
        string Filter
    ,
        params System.Tuple<string,object>\[\] Parameters
    )
    {
        T result = default(T) ;
        
        System.Data.IDbCommand cmd = dbc.CreateCommand() ;
        cmd.CommandText = "SELECT Id FROM Account WHERE " + Filter ;
        if ( Parameters != null )
        {
            foreach ( System.Tuple<string,object> p in Parameters )
            {
                System.Data.IDbDataParameter prm = cmd.CreateParameter() ;
                prm.ParameterName = p.Item1 ;
                prm.Value = p.Item2 ;
                cmd.Parameters.Add ( prm ) ;
            }
        }
        dbc.Open() ;
        object o = cmd.ExecuteScalar() ;
        if ( o != System.DBNull.Value )
        {
            result = (T) o ;
        }
        dbc.Close() ;
        return ( result ) ;
    }

S Douglas

Interesting, those are not very far off from I have used in code. Though most of my work is all database level work now (data warehousing, stored procs, SSIS, SSAS and SSRS), so I've come to use stored procs for everything. Just seems easier to me.

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

PIEBALDconsult

S Douglas wrote:

I've come to use stored procs for everything

They are rarely the right tool for the job.

S Douglas

PIEBALDconsult wrote:

They are rarely the right tool for the job.

No other tool suits the needs, :). I don't disagree with you sediments. However, in my world its all that exists.

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.