December 10, 2013 at 11:05 am
I am trying to execute the dynamic sql query and getting the following error:
Must declare the scaler variable @mesg_out.
I already declared the @mesg_out , it is giving error when dynamic sql is executing. Can anyone proviide the alternate solution to this errror / fix to this issue. Thanks in adavance.
begin
declare
@org_dt date,
@chk_dt date,
@mesg_string varchar(4000),
@mesg_out varchar(4000),
@sql_string varchar(4000)
set @org_dt = CONVERT(date,'2014-10-12')
set @chk_dt = CONVERT(date,getdate())
select @mesg_out = 'Date Check Error:'
set @sql_string = 'select @mesg_string = 'Error : ' + @mesg_out
where @chk_dt <= @org_dt'
--select @v_string = 'Failed : ' + @v_error_msg where
@v_process_validation_target <= @v_step_dt
exec (@select_strg)
print @v_string
end
---
error I am getting :
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@mesg_out".
December 10, 2013 at 12:17 pm
Why do you need to use dynamic sql for this? I don't see anything in what you posted that would indicate you need to use dynamic sql.
The reason you are getting the error is because your variables are not in scope when you execute your dynamic string. You would need to declare those in the same scope.
Does something like this work?
declare
@org_dt date,
@chk_dt date,
@mesg_string varchar(4000),
@mesg_out varchar(4000),
@sql_string varchar(4000)
set @org_dt = CONVERT(date,'2014-10-12')
set @chk_dt = CONVERT(date,getdate())
select @mesg_out = 'Date Check Error:'
select @mesg_string = 'Error : ' + @mesg_out
where @chk_dt <= @org_dt
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 10, 2013 at 12:27 pm
The following query I want to genrate dynamically:
select @mesg_string = 'Error : ' + @mesg_out
where @chk_dt <= @org_dt
becuase the where conditions will be changing and messing will different for different conditions.
It is giving only when I am using dynamic query like
execute (sql_strg)
Any suggestions will be apreciated
December 10, 2013 at 12:37 pm
pgupta6644 (12/10/2013)
The following query I want to genrate dynamically:select @mesg_string = 'Error : ' + @mesg_out
where @chk_dt <= @org_dt
becuase the where conditions will be changing and messing will different for different conditions.
It is giving only when I am using dynamic query like
execute (sql_strg)
Any suggestions will be apreciated
Right...as I said before, based on what you posted there is no need to execute a dynamic query for this. Just changing the variables will change the output. Unless you are trying to look at making the columns dynamic, which your code does not do, you don't need dynamic sql here at all.
If you truly do need to use dynamic sql, the code you posted is vulnerable to sql injection.
Please take a few minutes and read the first article in my signature for best practices when posting a question if you need more detailed assistance.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 11, 2013 at 8:44 am
To answer your initial question, the @mesg_out was not declared within the scope of the SQL you are creating dynamically. You need to declare @mesg_out inside the quoted SQL.
To respond to the other issue at hand, it is my experience that SQL developers will over-utilize dynamic SQL where it isn't really necessary. I will bash on my code trying to avoid dynamic SQL. If all else fails and depending on where the SQL is going to be used, I will consider resorting to dynamic SQL.
Hope this helps.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
December 12, 2013 at 6:51 am
To add a little more to what previous posters have said, you should look into using sp_executesql if you are trying pass variable values between your stored procedure and the dynamic sql (that is, if you really need to use dynamic sql).
December 12, 2013 at 5:23 pm
I have to agree with earlier posters that you don't need dynamic SQL for this, but let's assume that we can't see everything you do and it is really needed after all.
In which case, what you're looking is probably something close to but probably not exactly like this:
declare @SQLParms NVARCHAR(MAX) = N'
@org_dt date,
@chk_dt date,
@mesg_string varchar(4000),
@mesg_out varchar(4000) OUTPUT',
@org_dt date = CONVERT(date,'2014-10-12'),
@chk_dt date = CONVERT(date,getdate()),
@mesg_string varchar(4000) = 'Date Check Error:',
@mesg_out varchar(4000),
@sql_string nvarchar(4000);
set @sql_string = 'select @mesg_out = 'Error : ' + @mesg_string
where @chk_dt <= @org_dt'
print @sql_string
exec @sql_string, @SQLParms,
@org_dt = @org_dt,
@chk_dt = @chk_dt,
@mesg_string = @mesg_string,
@mesg_out = @mesg_out;
Try looking up sp_executesql in MS Books on line for an explanation of how I've defined the @SQLParms, including the OUTPUT variable.
Pay close attention to the proper typing of @sql_string and @SQLParms because it's gonna get ya if you don't type them right.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply