increase CPU time

Hi All,
I've a problem regarding increased CPU time.....
I've a stored procedure which joins approx 6
table and i'm using table variable to hold data. Stored procedure is working
fine and absolutely OK but when it runs on LIVE server through Web
Application, It increase CPU time and increase READS Per runs.
i added SET NOCOUNT ON and SET NOCOUNT OFF
as well as i added WITH RECOMPILE option, but no use it is still giving too
much reads per run.
is anybody having solution of this problem? Please
reply me ASAP...
Sword is hanging on my neck ';' help me
Manish SukhijaManish
What reads? Logical?
How about indexes defined on the tables? Do you see the optimizer is
available to create an efficient execution plan , i mean it uses the
indexes?
Actually if want more accurate answer please post DDL+ Sample data
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:49ACC575-E519-4473-9B0C-E7337BF94B17@.microsoft.com...
> Hi All,
> I've a problem regarding increased CPU time.....
> I've a stored procedure which joins approx 6
> table and i'm using table variable to hold data. Stored procedure is
> working
> fine and absolutely OK but when it runs on LIVE server through Web
> Application, It increase CPU time and increase READS Per runs.
> i added SET NOCOUNT ON and SET NOCOUNT OFF
> as well as i added WITH RECOMPILE option, but no use it is still giving
> too
> much reads per run.
> is anybody having solution of this problem? Please
> reply me ASAP...
> Sword is hanging on my neck ';' help me
> Manish Sukhija|||Hi Uri,
as i defined that i've stored procedure which joins approximately 6
table and when it runs om live server and when it is being checked by hosted
party in Profiler it shows
database is producing a high amount of cpu usage as well as page
reads in a perticular Stored procedure...
then i put a index on a column of one of joined table,
it reduce some time, now i want to know that is this the only solution to
decrease CPU time or is there any other way around, if this is only way then
on which table and ofcourse on which column should i put index. As you know
Stored procedure joins 6 table and they are having so may columns.
so should i put index on all columns of 6
table but as fas as i know it's not best practise to put index on all column
s
it can make adverse afftect on performance.
make me right if i'm wrong... or is there any other
way to decrease CPU time in that Stored procedure.
If you want code of that Stored procedure
i'll send it to you...
Manish
"Uri Dimant" wrote:

> Manish
> What reads? Logical?
> How about indexes defined on the tables? Do you see the optimizer is
> available to create an efficient execution plan , i mean it uses the
> indexes?
> Actually if want more accurate answer please post DDL+ Sample data
>
>
>
> "Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in messag
e
> news:49ACC575-E519-4473-9B0C-E7337BF94B17@.microsoft.com...
>
>|||Manish
Do you have FOREIGN KEY constraints? Does the key of referncing table have
an index?
http://www.sql-server-performance.c...or_counters.asp
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:1A433AA4-D9A0-47E2-82A4-1EEB092DD27B@.microsoft.com...
> Hi Uri,
> as i defined that i've stored procedure which joins approximately
> 6
> table and when it runs om live server and when it is being checked by
> hosted
> party in Profiler it shows
> database is producing a high amount of cpu usage as well as page
> reads in a perticular Stored procedure...
> then i put a index on a column of one of joined table,
> it reduce some time, now i want to know that is this the only solution to
> decrease CPU time or is there any other way around, if this is only way
> then
> on which table and ofcourse on which column should i put index. As you
> know
> Stored procedure joins 6 table and they are having so may columns.
> so should i put index on all columns of 6
> table but as fas as i know it's not best practise to put index on all
> columns
> it can make adverse afftect on performance.
> make me right if i'm wrong... or is there any other
> way to decrease CPU time in that Stored procedure.
> If you want code of that Stored procedure
> i'll send it to you...
> Manish
>
> "Uri Dimant" wrote:
>|||You may want to look at DBCC SHOWCONTIG if you aren't already running some
kind of defrag maintenence on your indexes.
--
Regards,
Jamie
"Manish Sukhija" wrote:

> Hi All,
> I've a problem regarding increased CPU time.....
> I've a stored procedure which joins approx 6
> table and i'm using table variable to hold data. Stored procedure is worki
ng
> fine and absolutely OK but when it runs on LIVE server through Web
> Application, It increase CPU time and increase READS Per runs.
> i added SET NOCOUNT ON and SET NOCOUNT OFF
> as well as i added WITH RECOMPILE option, but no use it is still giving to
o
> much reads per run.
> is anybody having solution of this problem? Please
> reply me ASAP...
> Sword is hanging on my neck ';' help me
> Manish Sukhijasql

Incorrect syntax with Declare

Good morning
I hope this will be a simple one, but I am trying to write a view to query my SQL server database, setting a variable using "declare". The code runs fine and returns a number of records, but when I try to save it, it comes up with "Incorrect syntax near the keyword DECLARE", and will not save. I am a bit of a novice when it comes to SQL, but I don't understand why it runs, but won't save. Here is my variable:
DECLARE @.ANCHORDATE AS DATETIME
SET @.ANCHORDATE = CASE WHEN datepart(mm, getdate()) BETWEEN 4 AND 9 THEN dateadd(mm, 3, DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)) ELSE dateadd(mm, - 9,
DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)) END

Thanks for any help.I am not 100% sure but you can try not to use DECLARE and try and save it. Let me know if it helps.|||I have replaced all the instances of the variable with the actual definition, and the view runs and saves fine. I was hoping to be able to keep it simple by not having to type in the variable definition each time. I don't understand why it can run, but saving it returns an error.

Incorrect syntax near @File

I am using this bulk insert command in procedure below. I am passing variable @.File inside of the procedure and I do not know the right syntax for it. Could you pls help me. When I enter the path for the file like 'C:\imp_file.csv' it works.

Thanks

ALTER procedure sp_BulkInsert1
@.File varchar(1000)

AS

BULK INSERT SQL_Tests.dbo.xRSA FROM @.File
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
CODEPAGE = 'RAW',
TABLOCK
)Because the syntax requires a constant, you can't use a variable... At least not directly anyway!ALTER procedure sp_BulkInsert1
@.File varchar(1000)
AS

EXECUTE ('BULK INSERT SQL_Tests.dbo.xRSA FROM ''' + @.File + '''
WITH
(
DATAFILETYPE = ''char''
, FIELDTERMINATOR = '',''
, ROWTERMINATOR = ''\n''
, CODEPAGE = ''RAW''
, TABLOCK
)' )

RETURN
GO-PatP|||:) Because the syntax requires a constant, you can't use a variable... At least not directly anyway!ALTER procedure sp_BulkInsert1
@.File varchar(1000)
AS

EXECUTE ('BULK INSERT SQL_Tests.dbo.xRSA FROM ''' + @.File + '''
WITH
(
DATAFILETYPE = ''char''
, FIELDTERMINATOR = '',''
, ROWTERMINATOR = ''\n''
, CODEPAGE = ''RAW''
, TABLOCK
)' )

RETURN
GO-PatP

Why do I need to use Execute Command?|||If you check BOL for the syntax of the BULK INSERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp) command, you'll notice that the syntax requires a constant for the file name. The only way I know to make a variable appear as a constant is to execute it indirectly, via the EXECUTE statement. We're basically working around a limitation in the supoorted syntax.

-PatP|||Because it's dynamic sql...

Pat I can't get the injection in to the vien...maybe you can...I'm sure it can be done

USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(8000))
GO

CREATE PROC sp_BulkInsert1
@.File varchar(1000)
AS

EXECUTE ('BULK INSERT myTable99 FROM ''' + @.File + '''
WITH
(
DATAFILETYPE = ''char''
, FIELDTERMINATOR = '',''
, ROWTERMINATOR = ''\n''
, CODEPAGE = ''RAW''
, TABLOCK
)' )

RETURN
GO

DECLARE @.x varchar(1000)
SELECT @.x = 'c:\config.sys' + '''' + ' GO SELECT ' + '''' + 'Lets execute some damaging sql' + '''' + ' GO'
EXEC sp_BulkInsert1 @.x
GO

DROP PROC sp_BulkInsert1
DROP TABLE myTable99
GO|||I'd use one of my quote fixers. I'm having to shoot from the hip since my system is toast at the moment, but it goes something like:CREATE FUNCTION dbo.FixQuote(@.pcIn VARCHAR(8000)) RETURNS VARCHAR(8000)
BEGIN
RETURN Replace(@.pcIn, '''', ''')
ENDGiven that little function, you could wrap it around the parameter to inhibit code injection. Note that it is MUCH better to prevent the injection at the source (the client/middleware machine) rather than trying to inhibit it at SQL Server.

-PatP

Incorrect syntax near '?' when trying to use parameters

I must be missing something simple. I have the following code that is not too complicated. I am trying to read a session variable (referenced in the <selectparameters> section) and use it to filter my SELECT statement. The select statement runs fine and displays everything in the gridview control until I put the "WHERE PackagingItemNo = ?" clause in. Then I get the error message in the title. I've tried using quotes, brackets, etc. to see if there's some syntax issue I'm missing here but I'm lost. I see numerous code examples that look identical to mine. What am I missing?

I'm mostly an Oracle and PL/SQL type so I'm a little lost here...

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BorderColor="Black"

AllowPaging="true" DataKeyNames="InBoundID" BorderStyle="Solid" BorderWidth="1px"

Width="100%" AllowSorting="True" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display.">

<HeaderStyle HorizontalAlign="Left" />

<Columns>

<asp:BoundField DataField="PackagingItemNo" HeaderText="Pack.Item#" SortExpression="PackagingItemNo" />

<asp:BoundField DataField="QuantityShipped" HeaderText="L" SortExpression="QuantityShipped" />

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyDBConnectionString1 %>"

ProviderName="<%$ ConnectionStrings:MyDBConnectionString1.ProviderName %>"

SelectCommand="SELECT [InBoundID], [ShipID], [ShipItemID], [LocationID], [ArtisanShipNo], [ArrivalDate], [ArrivalTime], [ShipMode], [PackagingItemNo], [QuantityIn], [QuantityShipped], [QuantityClaimed], [ContainerType], [UnloadInvoicedYN], [CarrierName], [BillOfLading], [ShippingPointName], [ShippingPointState], [ReleaseNumber], [ProfileFlag], [TagFlag], [ActiveYN], [WHouseUserID], [UpdatedOn], [UpdateIs] FROM [WHouseInBound] WHERE PackagingItemNo = ?">

<SelectParameters>

<asp:SessionParameter Name="PackItemNo" SessionField="PackagingItemNo" DefaultValue="12345" />

</SelectParameters>

</asp:SqlDataSource>

Update, I changed WHERE PackagingItemNo = ?

to WHERE PackagingItemNo = @.PackItemNo

and now the DefaultValue value from the <SelectParemeters><SessionParameter> property is used in the query. However, the actual session value isn't used, just whatever the DefaultValue property is set to. Using ResponseWrite to display Session("PackItemNo") does display the session value as it was set by previous pages, however, so I'm not sure what I'm missing now.

|||

Hi,

I too faced the same problem while using the parameterized query in asp:SqlDataSource

I found the following solution

My Command was

SelectCommand="SELECT DISTINCT Suppliers.CompanyName FROM Suppliers INNER JOIN Products ON Products.SupplierID = Suppliers.SupplierID WHERE Products.CategoryID = @.category ORDER BY CompanyName;"

and my Parameters are like this.

<SelectParameters>

<asp:SessionParameter SessionField="category" Type=Int64 Name="category" DefaultValue=1 />

</SelectParameters>

Be sure to match your parameter names. It takes the default value 1 for the first time, then it changes according to the session variable value. Hope u have solved it too.

Incorrect syntax near '?' when trying to use parameters

I must be missing something simple. I have the following code that is not too complicated. I am trying to read a session variable (referenced in the <selectparameters> section) and use it to filter my SELECT statement. The select statement runs fine and displays everything in the gridview control until I put the "WHERE PackagingItemNo = ?" clause in. Then I get the error message in the title. I've tried using quotes, brackets, etc. to see if there's some syntax issue I'm missing here but I'm lost. I see numerous code examples that look identical to mine. What am I missing?

I'm mostly an Oracle and PL/SQL type so I'm a little lost here...

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BorderColor="Black"

AllowPaging="true" DataKeyNames="InBoundID" BorderStyle="Solid" BorderWidth="1px"

Width="100%" AllowSorting="True" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display.">

<HeaderStyle HorizontalAlign="Left" />

<Columns>

<asp:BoundField DataField="PackagingItemNo" HeaderText="Pack.Item#" SortExpression="PackagingItemNo" />

<asp:BoundField DataField="QuantityShipped" HeaderText="L" SortExpression="QuantityShipped" />

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyDBConnectionString1 %>"

ProviderName="<%$ ConnectionStrings:MyDBConnectionString1.ProviderName %>"

SelectCommand="SELECT [InBoundID], [ShipID], [ShipItemID], [LocationID], [ArtisanShipNo], [ArrivalDate], [ArrivalTime], [ShipMode], [PackagingItemNo], [QuantityIn], [QuantityShipped], [QuantityClaimed], [ContainerType], [UnloadInvoicedYN], [CarrierName], [BillOfLading], [ShippingPointName], [ShippingPointState], [ReleaseNumber], [ProfileFlag], [TagFlag], [ActiveYN], [WHouseUserID], [UpdatedOn], [UpdateIs] FROM [WHouseInBound] WHERE PackagingItemNo = ?">

<SelectParameters>

<asp:SessionParameter Name="PackItemNo" SessionField="PackagingItemNo" DefaultValue="12345" />

</SelectParameters>

</asp:SqlDataSource>

Update, I changed WHERE PackagingItemNo = ?

to WHERE PackagingItemNo = @.PackItemNo

and now the DefaultValue value from the <SelectParemeters><SessionParameter> property is used in the query. However, the actual session value isn't used, just whatever the DefaultValue property is set to. Using ResponseWrite to display Session("PackItemNo") does display the session value as it was set by previous pages, however, so I'm not sure what I'm missing now.

|||

Hi,

I too faced the same problem while using the parameterized query in asp:SqlDataSource

I found the following solution

My Command was

SelectCommand="SELECT DISTINCT Suppliers.CompanyName FROM Suppliers INNER JOIN Products ON Products.SupplierID = Suppliers.SupplierID WHERE Products.CategoryID = @.category ORDER BY CompanyName;"

and my Parameters are like this.

<SelectParameters>

<asp:SessionParameter SessionField="category" Type=Int64 Name="category" DefaultValue=1 />

</SelectParameters>

Be sure to match your parameter names. It takes the default value 1 for the first time, then it changes according to the session variable value. Hope u have solved it too.

Incorrect sql statement - not reading variable

In the funtcion below I am selecting a value from the page - Request.QueryString("ProjectID") and this is being pulled through correctly - if I debug then ProjectID = Request.QueryString ("ProjectID") does equal the correct value.

However this value is not then working in the following line:

strSQL ="SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"

This statement is not reading any value for ProjectID and so is selecting all from the table....

How do I write this statement to pick up the variable above???

Thanks in advance for your help!

Clare

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

Dim dsAsNew Data.DataSet

Dim daAs Data.SqlClient.SqlDataAdapter

Dim strSQLAsString

Dim ProjectIDAs Int32

ProjectID = Request.QueryString(

"ProjectID")

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"Dim connStringAsString ="Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx"

da =

New Data.SqlClient.SqlDataAdapter(strSQL, connString)

da.Fill(ds)

ds.Tables(0).Columns.Add(

"imgFile")

ForEach tempRowAs Data.DataRowIn ds.Tables(0).Rows

tempRow.Item(

"imgFile") = ("imgGrab2.aspx?id=" & tempRow.Item("ProjectID"))Next

ImgGrid3.DataSource = ds

ImgGrid3.DataBind()

EndSub

hi,

try this

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = @.projectID"

Dim p As New System.Data.SqlClient.SqlParameter()
p.ParameterName = "@.projectID"
p.Value =ProjectID
p.SqlDbType =System .Data .SqlDbType .Char ;

yourcommand.Pramaters.Add(p)

|||

Thanks for your quick response but this is not working. I do not have a sql command declared, I am using strSQL ( a string), and da (a sqldataadaptor).

Can you help??

Thanks very much

|||

hi cabby

use following

Dim da As Data.SqlClient.SqlDataAdapterDim strSQL As StringDim ProjectID As Int32 ProjectID = Request.QueryString("ProjectID") strSQL ="SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = @.ProjectID" dim sqlcmdas new sqlcommand(strsql, connstring)sqlcmd.parameter.addwithvalue("ProjectID", request.querystring("projectid"));Dim connString As String ="Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx" da = New Data.SqlClient.SqlDataAdapter(sqlcmd) da.Fill(ds)in case your project idis char/varchar field use followingas parametervaluesqlcmd.parameter.addwithvalue("ProjectID","'" + request.querystring("projectid") +"'" ) 'see its included in single quotes

HTH

satish.

|||Hiya,|||

Hiya,

Thanks for the response. Sorry if I am asking simple questions but I have tried your code and am getting the following error:

Dim

sqlcmdAsNew sqlcommand(strSQL, connstring) - - - type 'sqlcommand' is not defined... so I change this to

Dim

sqlcmdAsNew Data.SqlClient.SqlCommand(strSQL, connString) -- and then I get connString - value of type string cannot be converted to 'System.Data.SqlClient.SqlConnection' and I also get an error on the next line

sqlcmd.parameter.addwithvalue(

"ProjectID", Request.QueryString("projectid")) -- parameter is not a member of 'System.Data.SqlClient.SqlConnection'

My code now is:

Dim

dsAsNew Data.DataSetDim daAs Data.SqlClient.SqlDataAdapterDim strSQLAsStringDim ProjectIDAs Int32

ProjectID = Request.QueryString(

"ProjectID")

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [@.ProjectID]"Dim sqlcmdAsNew Data.SqlClient.SqlCommand(strSQL, connString)

sqlcmd.parameter.addwithvalue(

"ProjectID", Request.QueryString("projectid"))Dim connStringAsString ="xxxxxxxxx"

da =

New Data.SqlClient.SqlDataAdapter(sqlcmd)

da.Fill(ds)

ds.Tables(0).Columns.Add(

"imgFile")

Thanks for the help!

Clare

|||first of all make sure you've imported system.data.sqlclient.

DimdsAsNew Data.DataSet

Dim daAs Data.SqlClient.SqlDataAdapter

Dim strSQLAsString

Dim ProjectIDAs Int32

ProjectID = Request.QueryString(

"ProjectID")

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [@.ProjectID]"

Dim sqlcmdAsNew Data.SqlClient.SqlCommand(strSQL,new sqlconnection(connString) )

sqlcmd.parameters.addwithvalue(

"ProjectID", ProjectID) //make sure variable projectid is not null

Dim connStringAsString ="xxxxxxxxx"

da =

New Data.SqlClient.SqlDataAdapter(sqlcmd)

da.Fill(ds)

ds.Tables(0).Columns.Add(

"imgFile")

hope it will help now.

thanks,

satish.

|||

Thanks again, really appreciate the help.

Nearly there now I think...

I am getting to the line:

da.Fill(ds)

and getting the error:

Invalid column name'@.ProjectID'.

Any ideas?

Thanks

Clare

|||

hi,

try this

Dim p As New System.Data.SqlClient.SqlParameter()
p.ParameterName = "@.projectID"
p.Value =ProjectID
p.SqlDbType =System .Data .SqlDbType .Char ;

sqlcmd.Pramaters.Add(p)

|||

errr sorry appologies for silly mistake of me change following line of my code

sqlcmd.parameters.addwithvalue("ProjectID", ProjectID) //make sure variable projectid is not null

to

sqlcmd.parameters.addwithvalue("@.ProjectID", ProjectID) //make sure variable projectid is not null

thanks,

satish.

|||

Hi,

I am still getting the same error. Do I need to declare in my sub function header that it is taking in a parameter? And if so how do I write this on page load?

Thanks

Clare

|||

After all this I can just use:

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] =" & Request.QueryString("ProjectID")

Thanks for all the help

|||

hi

try this, it works fine

Dim p As New System.Data.SqlClient.SqlParameter()
p.ParameterName = "@.projectID"
p.Value =Request.QueryString("ProjectID")

p.SqlDbType =Data .SqlDbType .String;

sqlcmd.Pramaters.Add(p)

In(@variable) clause and TABLE datatype variable

Hi this question has been asked several times and some solution has been
provided already. But the one I am facing is with a twist. I need to use the
IN() clause with a variable as its parameter. The variable is a list of comm
a
separated character values all enclosed in pairs of single quotes. I could
have solved this problem by enclosing the final query in a single quote and
running Exec command on it (with the Variable list outside the quotes) but I
also need to use a Table data type variable which raises error when EXEC
command is run.
Followig is the example that may explain well.
I have oversimplified this example and it does things that we would not do
in normal situation
use pubs;
-- declare and set Table variable
declare @.TableVariable TABLE ( col char(4) );
INSERT @.TableVariable
Select pub_id FROM publishers
;
--declare and set CSV single quoted characters list
declare @.ListVariable varchar(100);
set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
publishers table, promise
--the query where the Table variable is used as well as the IN() clause is
used
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN (@.ListVariable)
-- returns 0 rows
--if we use Exec by replacing the last code section above with as following
declare @.command varchar(2000)
set @.command='
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN ('+@.ListVariable+')
'
exec (@.command)
--Then we get the error message:
-- Must declare the variable '@.TableVariable'.
Expand AllCollapse All
Manage Your Profile |Legal |Contact Us |MSDN Flash Newsletterhttp://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
--
"Aamir Ghanchi" <AamirGhanchi@.discussions.microsoft.com> wrote in message
news:D82370C5-6E5E-4178-98B8-0017FE88E809@.microsoft.com...
> Hi this question has been asked several times and some solution has been
> provided already. But the one I am facing is with a twist. I need to use
> the
> IN() clause with a variable as its parameter. The variable is a list of
> comma
> separated character values all enclosed in pairs of single quotes. I could
> have solved this problem by enclosing the final query in a single quote
> and
> running Exec command on it (with the Variable list outside the quotes) but
> I
> also need to use a Table data type variable which raises error when EXEC
> command is run.
> Followig is the example that may explain well.
> I have oversimplified this example and it does things that we would not do
> in normal situation
> use pubs;
> -- declare and set Table variable
> declare @.TableVariable TABLE ( col char(4) );
> INSERT @.TableVariable
> Select pub_id FROM publishers
> ;
> --declare and set CSV single quoted characters list
> declare @.ListVariable varchar(100);
> set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
> publishers table, promise
> --the query where the Table variable is used as well as the IN() clause is
> used
> Select TableVariable.col
> From @.TableVariable as TableVariable
> Where TableVariable.col IN (@.ListVariable)
> -- returns 0 rows
> --if we use Exec by replacing the last code section above with as
> following
> declare @.command varchar(2000)
> set @.command='
> Select TableVariable.col
> From @.TableVariable as TableVariable
> Where TableVariable.col IN ('+@.ListVariable+')
> '
> exec (@.command)
> --Then we get the error message:
> -- Must declare the variable '@.TableVariable'.
>
> Expand AllCollapse All
>
> Manage Your Profile |Legal |Contact Us |MSDN Flash Newsletter
>

IN(@variable) clause and Table Data Type variable

Hi this question has been asked several times and some solution has been
provided already. But the one I am facing is with a twist. I need to use the
IN() clause with a variable as its parameter. The variable is a list of comm
a
separated character values all enclosed in pairs of single quotes. I could
have solved this problem by enclosing the final query in a single quote and
running Exec command on it (with the Variable list outside the quotes) but I
also need to use a Table data type variable which raises error when EXEC
command is run.
Followig is the example that may explain well.
I have oversimplified this example and it does things that we would not do
in normal situation
use pubs;
-- declare and set Table variable
declare @.TableVariable TABLE ( col char(4) );
INSERT @.TableVariable
Select pub_id FROM publishers
;
--declare and set CSV single quoted characters list
declare @.ListVariable varchar(100);
set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
publishers table, promise
--the query where the Table variable is used as well as the IN() clause is
used
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN (@.ListVariable)
-- returns 0 rows
--if we use Exec by replacing the last code section above with as following
declare @.command varchar(2000)
set @.command='
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN ('+@.ListVariable+')
'
exec (@.command)
--Then we get the error message:
-- Must declare the variable '@.TableVariable'.
Expand AllCollapse All
Manage Your Profile |Legal |Contact Us |MSDN Flash NewsletterI'd suggest using something like this. in (select id from tablname)
in your dynamic SQL example don't use the variable.
Set @.command = ' Select TableVariable.col
>From @.TableVariable as TableVariable
Where TableVariable.col IN ('
loop on list
select @.command = @.command + each number
End
select @.command = @.command + ')'
exec (@.command)|||Use a temp table instead.
David Gugick
Imceda Software
www.imceda.com|||http://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
--
"Aamir Ghanchi" <AamirGhanchi@.discussions.microsoft.com> wrote in message
news:EAE233F0-A1C4-4CEF-9F72-8EB4EB4884E2@.microsoft.com...
> Hi this question has been asked several times and some solution has been
> provided already. But the one I am facing is with a twist. I need to use
> the
> IN() clause with a variable as its parameter. The variable is a list of
> comma
> separated character values all enclosed in pairs of single quotes. I could
> have solved this problem by enclosing the final query in a single quote
> and
> running Exec command on it (with the Variable list outside the quotes) but
> I
> also need to use a Table data type variable which raises error when EXEC
> command is run.
> Followig is the example that may explain well.
> I have oversimplified this example and it does things that we would not do
> in normal situation
> use pubs;
> -- declare and set Table variable
> declare @.TableVariable TABLE ( col char(4) );
> INSERT @.TableVariable
> Select pub_id FROM publishers
> ;
> --declare and set CSV single quoted characters list
> declare @.ListVariable varchar(100);
> set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
> publishers table, promise
> --the query where the Table variable is used as well as the IN() clause is
> used
> Select TableVariable.col
> From @.TableVariable as TableVariable
> Where TableVariable.col IN (@.ListVariable)
> -- returns 0 rows
> --if we use Exec by replacing the last code section above with as
> following
> declare @.command varchar(2000)
> set @.command='
> Select TableVariable.col
> From @.TableVariable as TableVariable
> Where TableVariable.col IN ('+@.ListVariable+')
> '
> exec (@.command)
> --Then we get the error message:
> -- Must declare the variable '@.TableVariable'.
>
> Expand AllCollapse All
>
> Manage Your Profile |Legal |Contact Us |MSDN Flash Newsletter
>|||On Mon, 7 Feb 2005 14:31:03 -0800, Aamir Ghanchi wrote:
(snip)
I have already answered this question in .server, even before you posted
it (twice!) to this group. Please don't multi-post.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Good thought. and this is what I did initially. but my IN() clause is
enclosed within the aggregate function SUM and subquery is not allowed in it
.
Thanks though.
"Paul Moore" wrote:

> I'd suggest using something like this. in (select id from tablname)
>
> in your dynamic SQL example don't use the variable.
> Set @.command = ' Select TableVariable.col
> Where TableVariable.col IN ('
> loop on list
> select @.command = @.command + each number
> End
> select @.command = @.command + ')'
> exec (@.command)
>|||slow though, but I guess thats the only viable option I am left with.
Thank you.
"David Gugick" wrote:

> Use a temp table instead.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||I understand. I just started using MSDN website for posting messages since
the new Google Groups interface eats up all the indentation on code snippets
.
Bad thing, I can't crosspost through MSDN site :(
thanks.
"Hugo Kornelis" wrote:

> On Mon, 7 Feb 2005 14:31:03 -0800, Aamir Ghanchi wrote:
> (snip)
> I have already answered this question in .server, even before you posted
> it (twice!) to this group. Please don't multi-post.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||>> but my IN() clause is enclosed within the aggregate function SUM
and subquery is not allowed in it. <<
What did you think that the arithmetic sum of a logical expression
would be anyway? Think about it for two seconds.