easy table based update statement??

Hello,

I have 2 ways of updating data I'm using often

1) via a cursor on TABLE1 update fields in TABLE2
2) via an some of variables ...

SELECT @.var1=FLD1, @.var2=FLD2 FROM TABLE1 WHERE FLD-ID = @.inputVAR
UPDATE TABLE2
SET FLDx = @.var1, FLDy = @.var2
WHERE ...

Now I have a system with 2 databases and I need to update table DB2.TAB
based on data in DB1.TAB. Instead of using 1 of the 2 ways I normally use,
I thought it would be much easier to get the required data immediately from
DB1.TAB in the update-statement of DB2.TAB ... but the way to do that
confuses me. I've checked books online and a lot of newsgrouppostings
giving good information but still I keep getting errors like this ...

The column prefix 'x.ADS' does not match with a table name or alias name
used in the query.

while executing the following statement ...

UPDATE DB2.dbo.TAB
SET
FLD1 = x.FLD1,
FLD2 = x.FLD2,
...
FROM DB1.dbo.TAB x, DB2.dbo.ADS
WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
@.InputParameter

So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
table DB1.TAB
AND I only want to update the row in DB2.TAB with the unique keyfield
IDOFTAB2 equal to variable @.InputParameter

Do you see what I'm doing wrong?

--
Thank you,
Kind regards,
Perre Van Wilrijk,
Remove capitals to get my real email address,Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> The column prefix 'x.ADS' does not match with a table name or alias name
> used in the query.
> while executing the following statement ...
> UPDATE DB2.dbo.TAB
> SET
> FLD1 = x.FLD1,
> FLD2 = x.FLD2,
> ...
> FROM DB1.dbo.TAB x, DB2.dbo.ADS
> WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
> @.InputParameter
> So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
> table DB1.TAB
> AND I only want to update the row in DB2.TAB with the unique keyfield
> IDOFTAB2 equal to variable @.InputParameter

The string x.ADS is not in the part of the query you posted. Maybe you
should post the complete query?

But what is really suspect is thaht DB.dbo.ADS is in the FROM lcause,
but not in the WHERE clause. That could cause some unexpectedly bad
performance, as you get a cartesian join.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply Mr Erland Sommarskog.

I changed the names of the fields and tables in order to eliminate dutch
databasenames and in order to avoid confusion about the tablename ADS which
appears in both databases, but which isn't an exact copy. DBIngeven is made
only to insert new rows. DBLezen is made only to read data earlier entered
in DBIngeven and other databases. So I'm sorry I made the statement unclear
by replacing tablename ADS by tablename TAB, forgetting to do it
everywhere.in the statement.

This is actually the statement I wrote to synchronize the data, the
statement causing the error "The column prefix 'x.ADS' does not match with a
table name or alias name used in the query.". Might the problem being
caused by the fact that both tables, being in different databases, have the
same name?

CREATE STORED PROCEDURE USP_SYNC
@.ADIDLezen int
AS
DECLARE @.ER int

UPDATE DBLezen.dbo.ADS
SET
ADS_HR = x.ADS_HR,
ADS_OR = x.ADS_OR,
ADS_VA = x.ADS_VA,
ADS_PH = x.ADS_PH,
ADS_GB = x.ADS_GB,
ADS_TELEPHONE = x.ADS_TELEPHONE,
ADS_GSM = x.ADS_GSM,
ADS_PHOTO = x.ADS_PHOTO,
ADS_USRID= x.ADS_USRID,
ADS_PRICE = x.ADS_PRICE,
ADS_PRICETYPE = x.ADS_PRICETYPE,
ADS_PRICEINDICATION = x.ADS_PRICEINDICATION,
ADS_REGION = x.ADS_REGION,
ADS_KGITEMID = x.ADS_KGITEMID,
ADS_PRODUCTTYPE = x.ADS_PRODUCTTYPE,
ADS_PRODUCTTYPE_WEB = x.ADS_PRODUCTTYPE_WEB,
ADS_FIL = x.ADS_FIL,
ADS_EOONLINE = x.ADS_ONLINE_END,
ADS_CHUS = 'ITOLUPD',
ADS_CHDT = getdate()
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
@.ADIDLezen

SET @.ER = @.@.ERROR

Thanks,
Perre.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95B3F1B592928Yazorman@.127.0.0.1...
> Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> > The column prefix 'x.ADS' does not match with a table name or alias name
> > used in the query.
> > while executing the following statement ...
> > UPDATE DB2.dbo.TAB
> > SET
> > FLD1 = x.FLD1,
> > FLD2 = x.FLD2,
> > ...
> > FROM DB1.dbo.TAB x, DB2.dbo.ADS
> > WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
> > @.InputParameter
> > So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
> > table DB1.TAB
> > AND I only want to update the row in DB2.TAB with the unique keyfield
> > IDOFTAB2 equal to variable @.InputParameter
> The string x.ADS is not in the part of the query you posted. Maybe you
> should post the complete query?
> But what is really suspect is thaht DB.dbo.ADS is in the FROM lcause,
> but not in the WHERE clause. That could cause some unexpectedly bad
> performance, as you get a cartesian join.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 3 Dec 2004 14:30:43 +0100, Perre Van Wilrijk wrote:

> WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
>@.ADIDLezen

Hi Perre,

Change x.ADS.ADID to x.ADID (or x.ADS_ADID - I can only guess here).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> I changed the names of the fields and tables in order to eliminate dutch
> databasenames and in order to avoid confusion about the tablename ADS
> which appears in both databases, but which isn't an exact copy.

Instead you caused confusion. (And Dutch is not a problem to understand,
at least as it's single. Double-dutch may be more difficult...)

> This is actually the statement I wrote to synchronize the data, the
> statement causing the error "The column prefix 'x.ADS' does not match
> with a table name or alias name used in the query.". Might the problem
> being caused by the fact that both tables, being in different databases,
> have the same name?

No, but because you the alias in the wrong place:

> FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
> WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
> @.ADIDLezen

x.ADS.ADID would refer to a table ADS owned by the user x.

A tip is to always use aliases. They usually make queries less verbose,
not the least when you use three-part names.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
Thanks Hugo,

Indead I wrote x.ADS.ADID instead of x.ADS_ADID. Unfortenatly the error
messages given back by SQL Server wasn't clear enough for me to locate the
synthax error I made. Great you saw it without knowing my table and field
names. Sorry to have bothered you with such a stupid mistake.

I also made an alias for DBLezen making the WHERE-clause better readable and
maintainable.
A little bit strange now I can't use the y alias in the SET clause (eg SET
y.ADS_HR = x.ADS_HR, ...), I guess that's because the first field name
refers to the table name after the UPDATE-word, which doesn't seem to be
aliasable. So this on works fine ...

UPDATE DBLezen.dbo.ADS
SET
ADS_HR = x.ADS_HR,
ADS_OR = x.ADS_OR,
ADS_VA = x.ADS_VA,
ADS_PH = x.ADS_PH,
ADS_GB = x.ADS_GB,
ADS_TELEPHONE = x.ADS_TELEPHONE,
ADS_GSM = x.ADS_GSM,
ADS_PHOTO = x.ADS_PHOTO,
ADS_USRID= x.ADS_USRID,
ADS_PRICE = x.ADS_PRICE,
ADS_PRICETYPE = x.ADS_PRICETYPE,
ADS_PRICEINDICATION = x.ADS_PRICEINDICATION,
ADS_REGION = x.ADS_REGION,
ADS_KGITEMID = x.ADS_KGITEMID,
ADS_PRODUCTTYPE = x.ADS_PRODUCTTYPE,
ADS_PRODUCTTYPE_WEB = x.ADS_PRODUCTTYPE_WEB,
ADS_FIL = x.ADS_FIL,
ADS_EOONLINE = x.ADS_ONLINE_END,
ADS_CHUS = 'ITOLUPD',
ADS_CHDT = getdate()
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS y
WHERE y.ADS_OVID = x.ADS_ADID AND y.ADS_ADID = @.ADIDLezen

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95B4AD8233CE9Yazorman@.127.0.0.1...
> Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> > I changed the names of the fields and tables in order to eliminate dutch
> > databasenames and in order to avoid confusion about the tablename ADS
> > which appears in both databases, but which isn't an exact copy.
> Instead you caused confusion. (And Dutch is not a problem to understand,
> at least as it's single. Double-dutch may be more difficult...)
> > This is actually the statement I wrote to synchronize the data, the
> > statement causing the error "The column prefix 'x.ADS' does not match
> > with a table name or alias name used in the query.". Might the problem
> > being caused by the fact that both tables, being in different databases,
> > have the same name?
> No, but because you the alias in the wrong place:
> > FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
> > WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID
=
> > @.ADIDLezen
> x.ADS.ADID would refer to a table ADS owned by the user x.
> A tip is to always use aliases. They usually make queries less verbose,
> not the least when you use three-part names.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 7 Dec 2004 13:55:55 +0100, Perre Van Wilrijk wrote:

(snip)
>I also made an alias for DBLezen making the WHERE-clause better readable and
>maintainable.
>A little bit strange now I can't use the y alias in the SET clause (eg SET
>y.ADS_HR = x.ADS_HR, ...), I guess that's because the first field name
>refers to the table name after the UPDATE-word, which doesn't seem to be
>aliasable. So this on works fine ...
> UPDATE DBLezen.dbo.ADS
> SET
> ADS_HR = x.ADS_HR,
(snip)

This should work as well:

UPDATE y
SET
ADS_HR = x.ADS_HR,
(...)
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS y
WHERE y.ADS_OVID = x.ADS_ADID AND y.ADS_ADID = @.ADIDLezen

Personally, I prefer to always use the UPDATE .. FROM syntax this way, so
I won't forget to use it if a self-join is included (than it becomes
mandatory to use the alias in the UPDATE clause).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> Indead I wrote x.ADS.ADID instead of x.ADS_ADID. Unfortenatly the error
> messages given back by SQL Server wasn't clear enough for me to locate the
> synthax error I made.

It's kind of difficult for SQL Server to second-guess what you really meant.

But it is true that the error messages from SQL Server are not always
crystal clear. When it comes to true parsing errors, the obscureness of
the messages partly comes from the too rich syntax of T-SQL. A typo can
lead to some legal syntax SQL that you are not aware of, but then lead
a syntax error further ahead.

For the error message you got, I guess the main problem is that the
message points to the first line in the UPDATE statement, instead of
the line where the error is. That makes it more difficult to spot the
error.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> It's kind of difficult for SQL Server to second-guess what you really
meant.
> But it is true that the error messages from SQL Server are not always
> crystal clear. When it comes to true parsing errors, the obscureness
of
> the messages partly comes from the too rich syntax of T-SQL. A typo
can
> lead to some legal syntax SQL that you are not aware of, but then
lead
> a syntax error further ahead.

It might not be easy to parse especially my code :-)
I can imagine that.
I once believed I could give people using my programs always clear
messages, for each kind of problem. We all have to learn.

Dynamically use variables in SQL in EXECUTE

Hi,
What I want to do is:
DECLARE @.sqlName varchar(255)
DECLARE @.temp NVARCHAR(100)
SET @.sqlName =(select name from master.dbo.sysdatabases where name like
'Job_%')
SET @.temp = 'USE ' + RTRIM(@.sqlName)
PRINT @.sqlName
EXEC (@.temp)
GO
--rest of my SQL code
--
Now basically I am going to have this script to run on multiple
databases where the database could be something different.
ex.
Computer1 - DB: Job_1234
Computer2 - DB: Job_5678
Before I run my code I want to make sure it runs under the correct
database. It finds the right database using select name from
master.dbo.sysdatabases where name like 'Job_%'
but how do I execute the USE @.temp statement. It says it executes, but
it still displays the master database in Query Analyzer. Any ideas on
how to do this? I just basically need to get this dynamic USE
statement to work. Thanks in advance.stuart.k...@.gmail.com wrote:
> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
Your code should work but the USE is scoped to the EXEC statement. Once
the EXEC is done you are returned to where you started. You need to put
some other code into the EXEC string as well if you want it to execute
in the context of another database.
EXEC is a pretty useless tool for this kind of thing. It's much easier
to parameterize the database in a connection string or at the OSQL
command prompt.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks a lot.
This worked if I ran something like
EXEC (@.temp + ' ' + @.code)
where @.code is the rest of my code that I wanted to run. I would use
OSQL if I could but unfortunately I can't.
Thanks again for your quick response.
-Stu
David Portas wrote:
> stuart.k...@.gmail.com wrote:
> Your code should work but the USE is scoped to the EXEC statement. Once
> the EXEC is done you are returned to where you started. You need to put
> some other code into the EXEC string as well if you want it to execute
> in the context of another database.
> EXEC is a pretty useless tool for this kind of thing. It's much easier
> to parameterize the database in a connection string or at the OSQL
> command prompt.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||You want to change context switching.
You can search sp_executeresultset on SQL Server 2000 SP3 later.
Not S2K5.
You can use below sample query.
DECLARE @.PROC NVARCHAR(4000)
SET @.PROC ='job_1234' + '.DBO.SP_EXECRESULTSET'
EXEC @.PROC @.SQLSTMT
"stuart.karp@.gmail.com"?? ??? ??:

> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
>

Dynamic Variables?

Hey,

I have two tables of data, table1 and table2. In Table1, there is a
"id" field, a "name" field, and an e-mail field. In table2, there is an
"id" field, and paramters that belong to the certain "id". When I want
to create a new record, I want to make both of the entries at the same
time and have their "id"s match. I thought that the best way of doing
this was to create the data in table1, then get the "id" field of that
entry by way of matching the "name", and then use that id for the "id"
field value for the entry to table2. The problem is this... I don't
know how to collect the value of table1.id and store it in a variable
to be sent with the rest of the data to table2. This is what I tried:

----------

@.name varchar(8000),
@.email varchar(8000)

INSERT INTO table1 (name, email)
VALUES (@.name, @.email)

SELECT id
FROM table1
WHERE table1.name = @.name

----------

>From there, I don't know what to do...SCOPE_IDENTITY function.

See Books Online for details.

--
David Portas
SQL Server MVP
--|||@.name varchar(8000),
@.email varchar(8000)

INSERT INTO table1 (name, email)
VALUES (@.name, @.email)

INSERT INTO table2 (id, p1, p2, p3)
VALUES (@.@.identity, 'val1', 'val2', 'val3')|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. IDENTITY cannot be a relational
key. Have you ever seen a CHAR(8000) name or email address? You will
now!

You need to learn how to design an RDBMS and at least learn to use the
right words. Throw this mess out and start over.|||Celko,

Did you have anything useful to add, or do you just like to hear
yourself talk? I was disapointed that your post didn't include a
shameless plug for your latest book -- although I can't imagine anyone
would pay for the privilege of your abuse when they can get it for free
on the newsgroups.

Dynamic Variables in DTS packages.

Hi,
I have built a DTS package to load a file into the database. I want the
package to be such that it can be run on any database. So, I tried
defining a Global Variable by creating a "Global Variable Task" and
creating a global variable for the "Initial Catalog" Property. But when
I try to run this package from the command line using the dtsrun
utility, the global variable is discarded and data is loaded into the
Database specified when I create the DTS. Is there a way to resolve
this? I also tried assigning it an Environmental Variable instead of
the Global Variable but it didn't work.
Thanks,
KPHi
There is no Global Variables task! Do you have a dynamic properties task to
assign the global variable to the property? Do you have workflow in place to
force the dynamic properties task to execute before the task for which the
properties are changed?
John
"kpraoasp@.yahoo.com" wrote:

> Hi,
> I have built a DTS package to load a file into the database. I want the
> package to be such that it can be run on any database. So, I tried
> defining a Global Variable by creating a "Global Variable Task" and
> creating a global variable for the "Initial Catalog" Property. But when
> I try to run this package from the command line using the dtsrun
> utility, the global variable is discarded and data is loaded into the
> Database specified when I create the DTS. Is there a way to resolve
> this? I also tried assigning it an Environmental Variable instead of
> the Global Variable but it didn't work.
> Thanks,
> KP
>

Dynamic Variables in DTS packages.

Hi,
I have built a DTS package to load a file into the database. I want the
package to be such that it can be run on any database. So, I tried
defining a Global Variable by creating a "Global Variable Task" and
creating a global variable for the "Initial Catalog" Property. But when
I try to run this package from the command line using the dtsrun
utility, the global variable is discarded and data is loaded into the
Database specified when I create the DTS. Is there a way to resolve
this? I also tried assigning it an Environmental Variable instead of
the Global Variable but it didn't work.
Thanks,
KPHi
There is no Global Variables task! Do you have a dynamic properties task to
assign the global variable to the property? Do you have workflow in place to
force the dynamic properties task to execute before the task for which the
properties are changed?
John
"kpraoasp@.yahoo.com" wrote:
> Hi,
> I have built a DTS package to load a file into the database. I want the
> package to be such that it can be run on any database. So, I tried
> defining a Global Variable by creating a "Global Variable Task" and
> creating a global variable for the "Initial Catalog" Property. But when
> I try to run this package from the command line using the dtsrun
> utility, the global variable is discarded and data is loaded into the
> Database specified when I create the DTS. Is there a way to resolve
> this? I also tried assigning it an Environmental Variable instead of
> the Global Variable but it didn't work.
> Thanks,
> KP
>

DYNAMIC TSQL

Here is the sample query:

DECLARE @.TABLENAME NVARCHAR(50);

DECLARE @.COL NVARCHAR(50);

DECLARE @.VALUE NVARCHAR(50);

/*** THESE VARIABLES WERE ARE BEING DYNAMICALLY FEEDED THROUGH CURSOR***/

DECLARE @.SQL1 NVARCHAR(1000);

SET SQL1 = 'SELECT * FROM' + @.TABLENAME + ' WHERE' + @. COL + '=" + @.VALUE

EXECUTE sp_executesql @.SQL1

Now when I execute this SP it gives me error invalid colunm name. I figure out that its because of the variable @.VALUE. Eventually I found out that I need to single quote the value of this dynamically feeded variable @. VALUE

is there any way I can do this; give quotes to @.value like @. COL + '=" + ' @.VALUE'

Use the following query,

Code Snippet

DECLARE@.TABLENAME NVARCHAR(50);

DECLARE@.COL NVARCHAR(50);

DECLARE@.VALUE NVARCHAR(50);

DECLARE@.SQL1 NVARCHAR(1000);

DECLARE@.PARAM NVARCHAR(1000);

SET @.SQL1= N'SELECT * FROM ' + @.TABLENAME + N' WHERE ' + @.COL + N'=@.VALUE';

SET @.PARAM = N'@.Value as Nvarchar(50)';

EXECUTEsp_executesql @.SQL1, @.PARAM, @.VALUE

Dynamic SQL with Datetime variables

I am having difficulty using dynamic SQL with datetime variables. A code
snippet like this gives me an error about converting a string to datetime:
...
WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
AND clutr_nm LIKE ' + @.Market + '
AND Convert(datetime, W) >= ' + @.startdate + ' '
@.startdate is the datetime variable. Using '' + @.startdate + '' prints the
literal variable @.startdate, while using ''' + @.startdate + ''' gives me the
same conversion error. How do I solve this?
Larry Menzin
American Techsystems Corp.The string concatination looks correct
probable you are looking for:
WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
AND clutr_nm LIKE ' + @.Market + '
AND W >= ' + datepart(w,@.startdate)
is this the one you are looking for? what should be the result of the query
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Larry Menzin" wrote:

> I am having difficulty using dynamic SQL with datetime variables. A code
> snippet like this gives me an error about converting a string to datetime:
> ...
> WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
> AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
> AND clutr_nm LIKE ' + @.Market + '
> AND Convert(datetime, W) >= ' + @.startdate + ' '
> @.startdate is the datetime variable. Using '' + @.startdate + '' prints th
e
> literal variable @.startdate, while using ''' + @.startdate + ''' gives me t
he
> same conversion error. How do I solve this?
> --
> Larry Menzin
> American Techsystems Corp.|||Dont no whether you defined the datetime as ISO (then the implicit
conversion will to add a number to a string which wouldnt be that nice).
How did you dfine your Datetime variable ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
Newsbeitrag news:40CFE544-2384-4BAD-9FB3-339A5D10BB88@.microsoft.com...
>I am having difficulty using dynamic SQL with datetime variables. A code
> snippet like this gives me an error about converting a string to datetime:
> ...
> WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
> AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
> AND clutr_nm LIKE ' + @.Market + '
> AND Convert(datetime, W) >= ' + @.startdate + ' '
> @.startdate is the datetime variable. Using '' + @.startdate + '' prints
> the
> literal variable @.startdate, while using ''' + @.startdate + ''' gives me
> the
> same conversion error. How do I solve this?
> --
> Larry Menzin
> American Techsystems Corp.|||Correction:
datepart(ww,@.startdate)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Chandra" wrote:
> The string concatination looks correct
> probable you are looking for:
> WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
> AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
> AND clutr_nm LIKE ' + @.Market + '
> AND W >= ' + datepart(w,@.startdate)
> is this the one you are looking for? what should be the result of the quer
y
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Larry Menzin" wrote:
>|||Doesn't work.
When using dynamic SQL, '+@.variable+' is used for numbers, '''+@.variable+'''
is used for strings, but what syntax is used for dates? I can't seem to find
anything that works.
Larry Menzin
American Techsystems Corp.
"Chandra" wrote:
> The string concatination looks correct
> probable you are looking for:
> WHERE cat LIKE ' + @.Product + ' AND W IS NOT NULL
> AND RegionID LIKE ' + @.Region + ' AND chnl_cd LIKE ' + @.Channel + '
> AND clutr_nm LIKE ' + @.Market + '
> AND W >= ' + datepart(w,@.startdate)
> is this the one you are looking for? what should be the result of the quer
y
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Larry Menzin" wrote:
>|||DECLARE @.startDate datetime
SET @.startDate = '4/1/2005' /* for testing */
I still can't get dynamic SQL to work using date variables embedded in the
SQL string.
Larry Menzin
American Techsystems Corp.
"Jens Sü?meyer" wrote:

> Don′t no whether you defined the datetime as ISO (then the implicit
> conversion will to add a number to a string which wouldn′t be that nice).
> How did you dfine your Datetime variable ?
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:40CFE544-2384-4BAD-9FB3-339A5D10BB88@.microsoft.com...
>
>|||Like mentioned in my previous post, the "Date" you are pasting in is in
some numeric format, so that SQL Server implicitly converts it to a number,
if you wanna add a number to a string (which cant be converted to a number)
the query parser will punish you with an error.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Larry,
What data type is column [W]?
Datetime values should be concatenated as characters values.
Example:
use northwind
go
declare @.i int
declare @.sql nvarchar(4000)
declare @.sd datetime
declare @.ed datetime
set @.sd = '19970101'
set @.ed = '19971231'
set @.sql = N'select @.i = count(*) from dbo.orders where orderdate > ''' +
convert(char(8), @.sd, 112) + N''' and orderdate < ''' + convert(char(8),
dateadd(day, 1, @.ed), 112) + N''''
print @.sql
exec sp_executesql @.sql, N'@.i int output', @.i output
print @.i
go
AMB
exec
"Larry Menzin" wrote:
> Doesn't work.
> When using dynamic SQL, '+@.variable+' is used for numbers, '''+@.variable+'
''
> is used for strings, but what syntax is used for dates? I can't seem to fi
nd
> anything that works.
>
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Chandra" wrote:
>|||Ok. I got it to work by converting datetime variables to character strings
and then comparing strings. Dates apparently cannot be used directly in
dynamic SQL. What is the performance hit from all these date to string
conversions?
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
> Larry,
>
> What data type is column [W]?
> Datetime values should be concatenated as characters values.
> Example:
> use northwind
> go
> declare @.i int
> declare @.sql nvarchar(4000)
> declare @.sd datetime
> declare @.ed datetime
> set @.sd = '19970101'
> set @.ed = '19971231'
> set @.sql = N'select @.i = count(*) from dbo.orders where orderdate > ''' +
> convert(char(8), @.sd, 112) + N''' and orderdate < ''' + convert(char(8),
> dateadd(day, 1, @.ed), 112) + N''''
> print @.sql
> exec sp_executesql @.sql, N'@.i int output', @.i output
> print @.i
> go
>
> AMB
> exec
> "Larry Menzin" wrote:
>|||> dynamic SQL. What is the performance hit from all these date to string
> conversions?
Can you show the code?. I have not idea what conversions are you talking
about.
AMB
"Larry Menzin" wrote:
> Ok. I got it to work by converting datetime variables to character strings
> and then comparing strings. Dates apparently cannot be used directly in
> dynamic SQL. What is the performance hit from all these date to string
> conversions?
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Alejandro Mesa" wrote:
>

Dynamic SQL or Table Variables?

Hi all,
I was wondering if anyone knew all the pros and cons about using table
variables instead of dynamic SQL, and vice versa'
Problem i have, is writing a stored procedure which allows users to
search for particular records in the database. This is fine when all
the columns are nvarchar, but when it also involves Id fields, its a
pain.
Example
tblCompany
Id - int
Name - nvarchar
Status - nvarchar
SalespersonId - int
I would like users to be able to search on Name, Status, SalespersonId
For the string fields i use something like this.
SELECT * FROM TBLCOMPANY
WHERE (
CASE
WHEN @.NAME <> '' THEN NAME
WHEN @.STATUS <> '' THEN STATUS
END
LIKE
CASE
WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
END)
But the user may or maynot have entered a SalespersonId. If they have
then i want to return records with that particular Id.
I was thinking of using the typical dynamic SQL approach as follows :
@.SQL = 'SELECT * FROM TBLCOMPANY WHERE '
IF @.NAME IS NOT NULL
SET @.SQL = '(NAME LIKE ' + @.NAME + ')'
IF @.STATUS IS NOT NULL
SET @.SQL = '(STATUS LIKE ' + @.STATUS + ')'
IF @.SALESPERSONID IS NOT NULL
SET @.SQL = '(SALESPERSONID = ' + @.SALESPERSONID + ')'
Then i thought about using the newish table variables, so i would
define the table and then run the following.
' CREATE TABLE VARIABLE HERE
INSERT INTO TABLE_VARIABLE
SELECT * FROM TBLCOMPANY
WHERE (
CASE
WHEN @.NAME <> '' THEN NAME
WHEN @.STATUS <> '' THEN STATUS
END
LIKE
CASE
WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
END)
IF @.SALESPERSONID IS NOT NULL
DELETE FROM TABLE_VARIABLE WHERE SALESPERSONID <> @.SALESPERSONID
What do you all think'Hi
http://www.sommarskog.se/dyn-search.html
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1163671811.306877.294670@.h54g2000cwb.googlegroups.com...
> Hi all,
> I was wondering if anyone knew all the pros and cons about using table
> variables instead of dynamic SQL, and vice versa'
> Problem i have, is writing a stored procedure which allows users to
> search for particular records in the database. This is fine when all
> the columns are nvarchar, but when it also involves Id fields, its a
> pain.
> Example
> tblCompany
> Id - int
> Name - nvarchar
> Status - nvarchar
> SalespersonId - int
> I would like users to be able to search on Name, Status, SalespersonId
> For the string fields i use something like this.
> SELECT * FROM TBLCOMPANY
> WHERE (
> CASE
> WHEN @.NAME <> '' THEN NAME
> WHEN @.STATUS <> '' THEN STATUS
> END
> LIKE
> CASE
> WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
> WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
> END)
> But the user may or maynot have entered a SalespersonId. If they have
> then i want to return records with that particular Id.
> I was thinking of using the typical dynamic SQL approach as follows :
> @.SQL = 'SELECT * FROM TBLCOMPANY WHERE '
> IF @.NAME IS NOT NULL
> SET @.SQL = '(NAME LIKE ' + @.NAME + ')'
> IF @.STATUS IS NOT NULL
> SET @.SQL = '(STATUS LIKE ' + @.STATUS + ')'
> IF @.SALESPERSONID IS NOT NULL
> SET @.SQL = '(SALESPERSONID = ' + @.SALESPERSONID + ')'
> Then i thought about using the newish table variables, so i would
> define the table and then run the following.
> ' CREATE TABLE VARIABLE HERE
> INSERT INTO TABLE_VARIABLE
> SELECT * FROM TBLCOMPANY
> WHERE (
> CASE
> WHEN @.NAME <> '' THEN NAME
> WHEN @.STATUS <> '' THEN STATUS
> END
> LIKE
> CASE
> WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
> WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
> END)
> IF @.SALESPERSONID IS NOT NULL
> DELETE FROM TABLE_VARIABLE WHERE SALESPERSONID <> @.SALESPERSONID
> What do you all think'
>

Dynamic SQL or Table Variables?

Hi all,
I was wondering if anyone knew all the pros and cons about using table
variables instead of dynamic SQL, and vice versa?
Problem i have, is writing a stored procedure which allows users to
search for particular records in the database. This is fine when all
the columns are nvarchar, but when it also involves Id fields, its a
pain.
Example
tblCompany
Id - int
Name - nvarchar
Status - nvarchar
SalespersonId - int
I would like users to be able to search on Name, Status, SalespersonId
For the string fields i use something like this.
SELECT * FROM TBLCOMPANY
WHERE (
CASE
WHEN @.NAME <> '' THEN NAME
WHEN @.STATUS <> '' THEN STATUS
END
LIKE
CASE
WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
END)
But the user may or maynot have entered a SalespersonId. If they have
then i want to return records with that particular Id.
I was thinking of using the typical dynamic SQL approach as follows :
@.SQL = 'SELECT * FROM TBLCOMPANY WHERE '
IF @.NAME IS NOT NULL
SET @.SQL = '(NAME LIKE ' + @.NAME + ')'
IF @.STATUS IS NOT NULL
SET @.SQL = '(STATUS LIKE ' + @.STATUS + ')'
IF @.SALESPERSONID IS NOT NULL
SET @.SQL = '(SALESPERSONID = ' + @.SALESPERSONID + ')'
Then i thought about using the newish table variables, so i would
define the table and then run the following.
' CREATE TABLE VARIABLE HERE
INSERT INTO TABLE_VARIABLE
SELECT * FROM TBLCOMPANY
WHERE (
CASE
WHEN @.NAME <> '' THEN NAME
WHEN @.STATUS <> '' THEN STATUS
END
LIKE
CASE
WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
END)
IF @.SALESPERSONID IS NOT NULL
DELETE FROM TABLE_VARIABLE WHERE SALESPERSONID <> @.SALESPERSONID
What do you all think?
Hi
http://www.sommarskog.se/dyn-search.html
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1163671811.306877.294670@.h54g2000cwb.googlegr oups.com...
> Hi all,
> I was wondering if anyone knew all the pros and cons about using table
> variables instead of dynamic SQL, and vice versa?
> Problem i have, is writing a stored procedure which allows users to
> search for particular records in the database. This is fine when all
> the columns are nvarchar, but when it also involves Id fields, its a
> pain.
> Example
> tblCompany
> Id - int
> Name - nvarchar
> Status - nvarchar
> SalespersonId - int
> I would like users to be able to search on Name, Status, SalespersonId
> For the string fields i use something like this.
> SELECT * FROM TBLCOMPANY
> WHERE (
> CASE
> WHEN @.NAME <> '' THEN NAME
> WHEN @.STATUS <> '' THEN STATUS
> END
> LIKE
> CASE
> WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
> WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
> END)
> But the user may or maynot have entered a SalespersonId. If they have
> then i want to return records with that particular Id.
> I was thinking of using the typical dynamic SQL approach as follows :
> @.SQL = 'SELECT * FROM TBLCOMPANY WHERE '
> IF @.NAME IS NOT NULL
> SET @.SQL = '(NAME LIKE ' + @.NAME + ')'
> IF @.STATUS IS NOT NULL
> SET @.SQL = '(STATUS LIKE ' + @.STATUS + ')'
> IF @.SALESPERSONID IS NOT NULL
> SET @.SQL = '(SALESPERSONID = ' + @.SALESPERSONID + ')'
> Then i thought about using the newish table variables, so i would
> define the table and then run the following.
> ' CREATE TABLE VARIABLE HERE
> INSERT INTO TABLE_VARIABLE
> SELECT * FROM TBLCOMPANY
> WHERE (
> CASE
> WHEN @.NAME <> '' THEN NAME
> WHEN @.STATUS <> '' THEN STATUS
> END
> LIKE
> CASE
> WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
> WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
> END)
> IF @.SALESPERSONID IS NOT NULL
> DELETE FROM TABLE_VARIABLE WHERE SALESPERSONID <> @.SALESPERSONID
> What do you all think?
>

Dynamic SQL or Table Variables?

Hi all,
I was wondering if anyone knew all the pros and cons about using table
variables instead of dynamic SQL, and vice versa'
Problem i have, is writing a stored procedure which allows users to
search for particular records in the database. This is fine when all
the columns are nvarchar, but when it also involves Id fields, its a
pain.
Example
tblCompany
Id - int
Name - nvarchar
Status - nvarchar
SalespersonId - int
I would like users to be able to search on Name, Status, SalespersonId
For the string fields i use something like this.
SELECT * FROM TBLCOMPANY
WHERE (
CASE
WHEN @.NAME <> '' THEN NAME
WHEN @.STATUS <> '' THEN STATUS
END
LIKE
CASE
WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
END)
But the user may or maynot have entered a SalespersonId. If they have
then i want to return records with that particular Id.
I was thinking of using the typical dynamic SQL approach as follows :
@.SQL = 'SELECT * FROM TBLCOMPANY WHERE '
IF @.NAME IS NOT NULL
SET @.SQL = '(NAME LIKE ' + @.NAME + ')'
IF @.STATUS IS NOT NULL
SET @.SQL = '(STATUS LIKE ' + @.STATUS + ')'
IF @.SALESPERSONID IS NOT NULL
SET @.SQL = '(SALESPERSONID = ' + @.SALESPERSONID + ')'
Then i thought about using the newish table variables, so i would
define the table and then run the following.
' CREATE TABLE VARIABLE HERE
INSERT INTO TABLE_VARIABLE
SELECT * FROM TBLCOMPANY
WHERE (
CASE
WHEN @.NAME <> '' THEN NAME
WHEN @.STATUS <> '' THEN STATUS
END
LIKE
CASE
WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
END)
IF @.SALESPERSONID IS NOT NULL
DELETE FROM TABLE_VARIABLE WHERE SALESPERSONID <> @.SALESPERSONID
What do you all think'Hi
http://www.sommarskog.se/dyn-search.html
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1163671811.306877.294670@.h54g2000cwb.googlegroups.com...
> Hi all,
> I was wondering if anyone knew all the pros and cons about using table
> variables instead of dynamic SQL, and vice versa'
> Problem i have, is writing a stored procedure which allows users to
> search for particular records in the database. This is fine when all
> the columns are nvarchar, but when it also involves Id fields, its a
> pain.
> Example
> tblCompany
> Id - int
> Name - nvarchar
> Status - nvarchar
> SalespersonId - int
> I would like users to be able to search on Name, Status, SalespersonId
> For the string fields i use something like this.
> SELECT * FROM TBLCOMPANY
> WHERE (
> CASE
> WHEN @.NAME <> '' THEN NAME
> WHEN @.STATUS <> '' THEN STATUS
> END
> LIKE
> CASE
> WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
> WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
> END)
> But the user may or maynot have entered a SalespersonId. If they have
> then i want to return records with that particular Id.
> I was thinking of using the typical dynamic SQL approach as follows :
> @.SQL = 'SELECT * FROM TBLCOMPANY WHERE '
> IF @.NAME IS NOT NULL
> SET @.SQL = '(NAME LIKE ' + @.NAME + ')'
> IF @.STATUS IS NOT NULL
> SET @.SQL = '(STATUS LIKE ' + @.STATUS + ')'
> IF @.SALESPERSONID IS NOT NULL
> SET @.SQL = '(SALESPERSONID = ' + @.SALESPERSONID + ')'
> Then i thought about using the newish table variables, so i would
> define the table and then run the following.
> ' CREATE TABLE VARIABLE HERE
> INSERT INTO TABLE_VARIABLE
> SELECT * FROM TBLCOMPANY
> WHERE (
> CASE
> WHEN @.NAME <> '' THEN NAME
> WHEN @.STATUS <> '' THEN STATUS
> END
> LIKE
> CASE
> WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
> WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
> END)
> IF @.SALESPERSONID IS NOT NULL
> DELETE FROM TABLE_VARIABLE WHERE SALESPERSONID <> @.SALESPERSONID
> What do you all think'
>