Problem Creating SP

Thanks to some help from this NG I now have a query that works in
identifying duplicates in a selected table. Now I am trying to build a
stored procedure so that I can automate the testing of multiple fields in
several tables. I am building the query using dynamic SQL and when I execute
it I get an error:
The name 'Select UnitName, ShowName, Eqp1Judge, Eqp1Voc From DCScores WHERE
((Eqp1Voc IN (Select Eqp1Voc FROM DCScores As tmp WHERE CircuitID = 501 AND
PorF = 'P' AND UnitClass = 'SW' Group By Eqp1Voc HAVING Count(*)>1)) AND
CircuitID = 501 AND PorF = 'P' AND UnitClass = 'SW') Order By Eqp1Voc' is
not a valid identifier.
The main part of that is the Select statement built within the SP. If I copy
and paste the query portion into QA, it runs fine with the desired result.
What does this error message mean?
The relavant part of the SP is as follows:
==================================
DECLARE
@.sql AS varchar(6000)
Set @.sql = 'Select UnitName, ShowName, ' + @.Judge + ', ' + @.SubCaption + '
From ' + @.table +
' WHERE ((' + @.SubCaption + ' IN (Select ' + @.SubCaption + ' FROM ' + @.table
+ ' As tmp WHERE ' +
' CircuitID = ' + @.Circuit + ' AND PorF = ''' + @.PF + ''' AND UnitClass =
''' + @.Class + ''' Group By ' +
@.SubCaption + ' HAVING Count(*)>1)) AND CircuitID = ' + @.Circuit + ' AND
PorF = ''' + @.PF + ''' AND UnitClass = ''' + @.Class +
''') Order By ' + @.SubCaption
Print @.SQL
EXEC @.sql
==================================
WayneWayne Wengert wrote:
<SNIP>
Put Parens around the variable in the EXEC. For example:
Exec (@.varname)
David Gugick
Imceda Software
www.imceda.com|||YOu have to use SP_EXECUTESQL
Variable has to be a nvarchar !
Look here for dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
news:eweTRmFQFHA.904@.tk2msftngp13.phx.gbl...
> Thanks to some help from this NG I now have a query that works in
> identifying duplicates in a selected table. Now I am trying to build a
> stored procedure so that I can automate the testing of multiple fields in
> several tables. I am building the query using dynamic SQL and when I
> execute
> it I get an error:
> The name 'Select UnitName, ShowName, Eqp1Judge, Eqp1Voc From DCScores
> WHERE
> ((Eqp1Voc IN (Select Eqp1Voc FROM DCScores As tmp WHERE CircuitID = 501
> AND
> PorF = 'P' AND UnitClass = 'SW' Group By Eqp1Voc HAVING Count(*)>1)) AND
> CircuitID = 501 AND PorF = 'P' AND UnitClass = 'SW') Order By Eqp1Voc' is
> not a valid identifier.
> The main part of that is the Select statement built within the SP. If I
> copy
> and paste the query portion into QA, it runs fine with the desired result.
> What does this error message mean?
> The relavant part of the SP is as follows:
> ==================================
> DECLARE
> @.sql AS varchar(6000)
> Set @.sql = 'Select UnitName, ShowName, ' + @.Judge + ', ' + @.SubCaption + '
> From ' + @.table +
> ' WHERE ((' + @.SubCaption + ' IN (Select ' + @.SubCaption + ' FROM ' +
> @.table
> + ' As tmp WHERE ' +
> ' CircuitID = ' + @.Circuit + ' AND PorF = ''' + @.PF + ''' AND UnitClass =
> ''' + @.Class + ''' Group By ' +
> @.SubCaption + ' HAVING Count(*)>1)) AND CircuitID = ' + @.Circuit + ' AND
> PorF = ''' + @.PF + ''' AND UnitClass = ''' + @.Class +
> ''') Order By ' + @.SubCaption
> Print @.SQL
> EXEC @.sql
> ==================================
> Wayne
>|||You are right.
"David Gugick" <davidg-nospam@.imceda.com> schrieb im Newsbeitrag
news:eapZEqFQFHA.3628@.TK2MSFTNGP12.phx.gbl...
> Wayne Wengert wrote:
> <SNIP>
> Put Parens around the variable in the EXEC. For example:
> Exec (@.varname)
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Thanks guys. That was it. Added the parens and it runs fine.
Wayne
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> wrote in message
news:eweTRmFQFHA.904@.tk2msftngp13.phx.gbl...
> Thanks to some help from this NG I now have a query that works in
> identifying duplicates in a selected table. Now I am trying to build a
> stored procedure so that I can automate the testing of multiple fields in
> several tables. I am building the query using dynamic SQL and when I
execute
> it I get an error:
> The name 'Select UnitName, ShowName, Eqp1Judge, Eqp1Voc From DCScores
WHERE
> ((Eqp1Voc IN (Select Eqp1Voc FROM DCScores As tmp WHERE CircuitID = 501
AND
> PorF = 'P' AND UnitClass = 'SW' Group By Eqp1Voc HAVING Count(*)>1)) AND
> CircuitID = 501 AND PorF = 'P' AND UnitClass = 'SW') Order By Eqp1Voc' is
> not a valid identifier.
> The main part of that is the Select statement built within the SP. If I
copy
> and paste the query portion into QA, it runs fine with the desired result.
> What does this error message mean?
> The relavant part of the SP is as follows:
> ==================================
> DECLARE
> @.sql AS varchar(6000)
> Set @.sql = 'Select UnitName, ShowName, ' + @.Judge + ', ' + @.SubCaption + '
> From ' + @.table +
> ' WHERE ((' + @.SubCaption + ' IN (Select ' + @.SubCaption + ' FROM ' +
@.table
> + ' As tmp WHERE ' +
> ' CircuitID = ' + @.Circuit + ' AND PorF = ''' + @.PF + ''' AND UnitClass =
> ''' + @.Class + ''' Group By ' +
> @.SubCaption + ' HAVING Count(*)>1)) AND CircuitID = ' + @.Circuit + ' AND
> PorF = ''' + @.PF + ''' AND UnitClass = ''' + @.Class +
> ''') Order By ' + @.SubCaption
> Print @.SQL
> EXEC @.sql
> ==================================
> Wayne
>