October 8, 2009 at 6:08 am
Hi,
Below query is giving error : "Must declare the scalar variable "@tblRules"."
please tell me how can i use table variable in the below scenario. i cant use temp table.
----------------------------------------------------
DECLARE
@ParamDef NVARCHAR(MAX),
@redirectSQL NVARCHAR(4000),
@acct_id int
SET @ParamDef = N'
@acct_id int,
@@tblRules table (
idx int,
thank_you_page_rule_stub uniqueidentifier,
trigger_value_filter_id smallint,
trigger_all_true_flag smallint
),
@thank_you_page_stub ut_stub'
select @redirectSQL = 'select 1 from dbo.vw_cvnt_Contact with (nolock)' +
' where acct_id = @acct_id' +
' and cont_stub = @cont_stub ' +
' and @rpt_field_header_dbcol ' +
' IN (Select trigger_choice from @tblRules where thank_you_page_stub =' +
'@thank_you_page_stub)'
--Execute the sql and check the count
exec sp_executesql @redirectSQL, @ParamDef,
@acct_id = @acct_id,
@tblRules = @tblRules,
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 8, 2009 at 6:46 am
Bhuvnesh (10/8/2009)
Hi,Below query is giving error : "Must declare the scalar variable "@tblRules"."
please tell me how can i use table variable in the below scenario. i cant use temp table.
----------------------------------------------------
DECLARE
@ParamDef NVARCHAR(MAX),
@redirectSQL NVARCHAR(4000),
@acct_id int
SET @ParamDef = N'
@acct_id int,
@@tblRules table (
idx int,
thank_you_page_rule_stub uniqueidentifier,
trigger_value_filter_id smallint,
trigger_all_true_flag smallint
),
@thank_you_page_stub ut_stub'
select @redirectSQL = 'select 1 from dbo.vw_cvnt_Contact with (nolock)' +
' where acct_id = @acct_id' +
' and cont_stub = @cont_stub ' +
' and @rpt_field_header_dbcol ' +
' IN (Select trigger_choice from @tblRules where thank_you_page_stub =' +
'@thank_you_page_stub)'
--Execute the sql and check the count
exec sp_executesql @redirectSQL, @ParamDef,
@acct_id = @acct_id,
@tblRules = @tblRules,
I've never tried this one, so I'm not sure it's possible, but it could be that @@TableRules_Table in the parameter definition.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 8, 2009 at 7:11 am
[font="Verdana"]
@@tblRules table remove the extra @. and let us know.
Mahesh[/font]
MH-09-AM-8694
October 8, 2009 at 8:10 am
no its not working in the either ways
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 8, 2009 at 8:42 am
You are passing a table variable to sp_executeSQL that does not exist at the time of the call. You will need to declare the table variable first:
DECLARE
@ParamDef NVARCHAR(MAX),
@redirectSQL NVARCHAR(4000),
@acct_id int
.....
declare @tblRules table (
idx int,
thank_you_page_rule_stub uniqueidentifier,
trigger_value_filter_id smallint,
trigger_all_true_flag smallint
)
insert into @tblRules (...)
select ...
--Execute the sql and check the count
exec sp_executesql @redirectSQL, @ParamDef,
@acct_id = @acct_id,
@tblRules = @tblRules,
...
then populate it somehow, and then pass it as a parameter to sp_executeSQL. The same for other variables you seem to use in @redirectSQL (@cont_stub, @thank_you_page_stub, etc.)
October 8, 2009 at 8:48 am
no
if you see my first mail
it includes ::::::::::
SET @ParamDef = N'
@acct_id int,
@tblRules table (
idx int,
thank_you_page_rule_stub uniqueidentifier,
trigger_value_filter_id smallint,
trigger_all_true_flag smallint
),
@thank_you_page_stub ut_stub'
it contains "@tblRules table " declaration .
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 8, 2009 at 9:09 am
Al-279884 is correct - you've missed the DECLARE statement from within the string ParamDef:
e.g:
DECLARE @ParamDef NVARCHAR(MAX)
SET @ParamDef = N'
DECLARE @tblRules table (
tt_ID varchar
)
insert into @tblRules(tt_ID)
VALUES (''a'')
select * from @tblRules'
--Execute the sql and check the count
exec sp_executesql @ParamDef
October 8, 2009 at 9:11 am
Bhuvnesh (10/8/2009)
noif you see my first mail
it includes ::::::::::
SET @ParamDef = N'
@acct_id int,
@tblRules table (
idx int,
thank_you_page_rule_stub uniqueidentifier,
trigger_value_filter_id smallint,
trigger_all_true_flag smallint
),
@thank_you_page_stub ut_stub'
it contains "@tblRules table " declaration .
that is the table declaration as a parameter, not as a variable within your current session. The idea is that sp_executesql opens up a new session and runs a script in it. When you run sp_executesql you specify "@tblRules = @tblRules". The first one refers to @tblRules you have specified in @paramDef, which exists only in the new session sp_executesql created. The second one should be a local variable in you current session that you are trying to pass as a parameter. This second table is not defined.
October 8, 2009 at 9:14 am
Table variables are only visible in the scope that they are created in. They are not visible inside dynamic SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2009 at 9:17 am
Al-279884 (10/8/2009)
that is the table declaration as a parameter, not as a variable within your current session. The idea is that sp_executesql opens up a new session and runs a script in it. When you run sp_executesql you specify "@tblRules = @tblRules". The first one refers to @tblRules you have specified in @paramDef, which exists only in the new session sp_executesql created. The second one should be a local variable in you current session that you are trying to pass as a parameter. This second table is not defined.
I don't think you can pass table variables around like that. Table variables don't support assignment operations and SQL 2005 doesn't have table-type parameters.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2009 at 9:23 am
GilaMonster (10/8/2009)
Al-279884 (10/8/2009)
that is the table declaration as a parameter, not as a variable within your current session. The idea is that sp_executesql opens up a new session and runs a script in it. When you run sp_executesql you specify "@tblRules = @tblRules". The first one refers to @tblRules you have specified in @paramDef, which exists only in the new session sp_executesql created. The second one should be a local variable in you current session that you are trying to pass as a parameter. This second table is not defined.I don't think you can pass table variables around like that. Table variables don't support assignment operations and SQL 2005 doesn't have table-type parameters.
I had my doubts whether you can pass table variables as parameters at all, but I was to lazy to try it myself.
Thanks
October 8, 2009 at 9:43 pm
Thanks for ALL i got the point
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply