Dynamic SQL Command: varchar

Showing posts with label varchar. Show all posts

Thursday, March 29, 2012

Easy way to index a column with URLs

We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?If you need to enforce uniqueness, or you are frequently searching the table
by the URL column, then an index is appropriate. Bear in mind that an index
key can be no more than 900 bytes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Kevin" <none@.none.com> wrote in message
news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?|||I'm afraid that some of the urls possibly could get over that size after
encoding (the URL column is nvarchar).
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
> If you need to enforce uniqueness, or you are frequently searching the
> table
> by the URL column, then an index is appropriate. Bear in mind that an
> index
> key can be no more than 900 bytes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Kevin" <none@.none.com> wrote in message
> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
> We have a column that stores URL (varchar), which ultimately are unique
> within the table (though an identity is the actual primary key). Is it
> good
> practice to put an index on such a wide column? Are there are other
> alternatives, possibly different data type to use?
>|||"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>> If you need to enforce uniqueness, or you are frequently searching the
>> table
>> by the URL column, then an index is appropriate. Bear in mind that an
>> index
>> key can be no more than 900 bytes.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Kevin" <none@.none.com> wrote in message
>> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
>> We have a column that stores URL (varchar), which ultimately are unique
>> within the table (though an identity is the actual primary key). Is it
>> good
>> practice to put an index on such a wide column? Are there are other
>> alternatives, possibly different data type to use?
>|||Kevin,
if you badly want this column be indexed (of those values that do not excede
the 900 bites limit), you can do some more work:
create an indexed view that have a pk column and the URL column, with the
URL records no longer than 900 bites, index on the URL column;
create an indexed view that have a pk column and the URL column, with the
URL records longer than 900 bites, index on the pk column;
chances are that you may have a handful of records in the second view so
that you do not care to index scan it. And you have index seek on the
shorter URL values. Then join the views with your base table.
HTH
Quentin
"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>> If you need to enforce uniqueness, or you are frequently searching the
>> table
>> by the URL column, then an index is appropriate. Bear in mind that an
>> index
>> key can be no more than 900 bytes.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Kevin" <none@.none.com> wrote in message
>> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
>> We have a column that stores URL (varchar), which ultimately are unique
>> within the table (though an identity is the actual primary key). Is it
>> good
>> practice to put an index on such a wide column? Are there are other
>> alternatives, possibly different data type to use?
>sql

Easy way to index a column with URLs

Thursday, March 22, 2012

Easy BCP question ;)

Hey all,

A nice easy BCP question for y'all...
I'm currently running:

DECLARE @.sql varchar(2000)

SET @.sql = 'BCP master..sysobjects OUT C:\sysobjects.csv -c -t, -T -S' + @.@.ServerName

EXEC master..xp_cmdshell @.sql

Which works fine!
But how on earth do I save it to my local PC?
May sound silly, but this is one of my first adventures into BCP and I'd rather not "pollute" the server with my test files!

Thankyall!Maybe if you ran the bcp command from your local command line? There is no real reason you need xp_cmdshell.|||indeed. another possibility is to write the file to a share (but then you are susceptible to network glitches of course)

BCP master..sysobjects OUT \\SOME_OTHER_SERVER\sysobjects.csv ...|||There is no real reason you need xp_cmdshell.

Can I have a hit off of that?

This is the way I always do this

Unless I'm having a problem, then I do command line to get a better error message

George, what do you mean by pollute?

You can use xp_... to delete the damn files as well|||The guys downstairs get antsy when I leave csv files on the server that I maintain.
They polluted one of our production servers with an image of a laptop once (15Gb when we had 15.1Gb free!) and ever since then are very quick to point out when I leave a 0.5Mb csv on a production server.

anyhow, enough office politics.
I wouldn't know what to do from the command line - I tried running the above straight in (ok, I had 11 seconds left of my working day - I wasn't thorough (or bothered)) and I just got a "BCP is not a recognized function" (or similar).

Anyhow, it's pretty much a non-issue I suppose because I just have to remember to delete them, or as Brett suggested, I will go digging for the xp_.. to delete the bugger when I have some play time.

Thanks all :)|||oh never mind|||The guys downstairs get antsy when I leave csv files on the server that I maintain.
They polluted one of our production servers with an image of a laptop once (15Gb when we had 15.1Gb free!) and ever since then are very quick to point out when I leave a 0.5Mb csv on a production server.

Scrubs

This message is too short|||Anyhow, it's pretty much a non-issue I suppose because I just have to remember to delete them, or as Brett suggested, I will go digging for the xp_.. to delete the bugger when I have some play time.

It's called DOS youngster and the command is DEL. Kids today and their GUIs.

anyhow, enough office politics.

just remember one thing...

crush your enemies, drive them before you, and hear the lamentations of their women|||Sean, I learned a lot in DOS... But it's been far too long since I've had to use it!
Nothing would sodding run through the GUI on my Windows 3.0 PC ;)

Dir /w :D

easily editting fields with more than 1024 characters

i suspect i know the answer to this already, but here goes anyway...

i have a table that has field of varchar(2048), which once in a blue moon i
need to edit the data manually (until a bad character parser validates the
data before it's written ;-) )
at the moment i'm doing this through enterprise manager (sql server 2000),
opening the table then filtering using where clauses etc to see the records
i'm interested in. i then edit the data direct in the results pane (purely
because it's quicker than entering the UPDATE transact SQL). this is fine
until i hit a record that has 1024 or more characters in the field. all i
can do is delete all the data. if i try and paste the same data into the
field again, it'll truncate the record to the first 1024 chars (unconfirmed)
despite the field being able to take double that.

i've googled this and the result basically said "don't be lazy, do it
through UPDATE transact SQL in the query analyser".

anyone know if that's my only option or is there a patch / whatever to allow
me to keep using entman as i lazily do at the mo?

cheers!dave (usenet@.polo.devilgas.com) writes:
> i suspect i know the answer to this already, but here goes anyway...
> i have a table that has field of varchar(2048), which once in a blue
> moon i need to edit the data manually (until a bad character parser
> validates the data before it's written ;-) ) at the moment i'm doing
> this through enterprise manager (sql server 2000), opening the table
> then filtering using where clauses etc to see the records i'm interested
> in. i then edit the data direct in the results pane (purely because it's
> quicker than entering the UPDATE transact SQL). this is fine until i hit
> a record that has 1024 or more characters in the field. all i can do is
> delete all the data. if i try and paste the same data into the field
> again, it'll truncate the record to the first 1024 chars (unconfirmed)
> despite the field being able to take double that.
> i've googled this and the result basically said "don't be lazy, do it
> through UPDATE transact SQL in the query analyser".
> anyone know if that's my only option or is there a patch / whatever to
> allow me to keep using entman as i lazily do at the mo?

As I just said in another post, the Open Table function in Enterprise
Manager is a convenience function and not a replacement for an application
or even in class with Access or Excel. There are several shortcomings
with Open Table. In this particular case, I believe there is a limit of
around 1000 characters io EM.

So you just start typing UPDATE commands. By the time, you've gotten some
exercise, you will find that that is faster in the long run, because
scripts are repeatable, while point-and-click GUIs are not.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9698EF7C2DE54Yazorman@.127.0.0.1...
> As I just said in another post, the Open Table function in Enterprise
> Manager is a convenience function and not a replacement for an application
> or even in class with Access or Excel. There are several shortcomings
> with Open Table. In this particular case, I believe there is a limit of
> around 1000 characters io EM.
> So you just start typing UPDATE commands. By the time, you've gotten some
> exercise, you will find that that is faster in the long run, because
> scripts are repeatable, while point-and-click GUIs are not.
> --
> 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. i can assure you that in this case, it's a lot faster doing
it from EM than transact SQL. by the time i've copied and pasted the 1000+
characters, id field etc etc then edit the chars i want to change in the SQL
it's considerably longer.
thankfully these changes are once in a blue moon and only affect a single
record.

Easiest way of combining multiple fields from different records into one record?

I have a table;

CREATE TABLE theLiterals (
theKey varchar (255) NOT NULL ,
theValue varchar (255) NULL
)
INSERT INTO theLiterals VALUES('defaultServer','\\MyServer\')
INSERT INTO theLiterals VALUES('defaultShare','MyShare\')
INSERT INTO theLiterals VALUES('defaultFolder','MyFolder\')
INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')

I then try;

SELECT
defaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,
defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,
defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,
defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue END
FROM theLiterals

and I get;

defaultServer defaultShare defaultFolder defaultFile
\\MyServer\ NULL NULL NULL
NULL MyShare\ NULL NULL
NULL NULL MyFolder\ NULL
NULL NULL NULL MyFile.dat

but I want it COALESCEd like this;

defaultServer defaultShare defaultFolder defaultFile
\\MyServer\ MyShare\ MyFolder\ MyFile.dat

...but my syntax is incorrect. Is there an efficient way of doing this.

I want to have a script/UDF where I can say...
GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')
and then my one-row recordset will be...

RS(0) will = '\\MyServer\'
RS(1) will = 'MyShare\'
RS(2) will = 'MyFolder\'
RS(3) will = 'MyFile.dat'

Thanks for any help!Just add MIN():

SELECT
defaultServer = MIN(CASE WHEN theKey = 'defaultServer' THEN theValue END),
defaultShare = MIN(CASE WHEN theKey = 'defaultShare' THEN theValue END),
defaultFolder = MIN(CASE WHEN theKey = 'defaultFolder' THEN theValue END),
defaultFile = MIN(CASE WHEN theKey = 'defaultFile' THEN theValue END)
FROM theLiterals

--
David Portas
SQL Server MVP
--|||Hi

Maybe:
SELECT A.defaultServer, B.defaultShare, C.defaultFolder, D.defaultFile
FROM
( SELECT theValue AS defaultServer
FROM theLiterals
WHERE theKey = 'defaultServer' ) A,
( SELECT theValue AS defaultShare
FROM theLiterals
WHERE theKey = 'defaultShare' ) B,
( SELECT theValue AS defaultFolder
FROM theLiterals
WHERE theKey = 'defaultFolder' ) C,
( SELECT theValue AS defaultFile
FROM theLiterals
WHERE theKey = 'defaultFile' ) D

SELECT
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultServer' ) AS defaultServer ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultShare' ) AS defaultShare ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultFolder' ) AS defaultFolder ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultFile' ) AS defaultFile

You should put a unique or primary key on theKey to make sure only one row
is returned.

John

"Steve" <steve.lin@.cognizantdesign.com> wrote in message
news:27b20cea.0407090955.690c2c8b@.posting.google.c om...
> I have a table;
> CREATE TABLE theLiterals (
> theKey varchar (255) NOT NULL ,
> theValue varchar (255) NULL
> )
> INSERT INTO theLiterals VALUES('defaultServer','\\MyServer\')
> INSERT INTO theLiterals VALUES('defaultShare','MyShare\')
> INSERT INTO theLiterals VALUES('defaultFolder','MyFolder\')
> INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')
>
> I then try;
> SELECT
> defaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,
> defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,
> defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,
> defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue END
> FROM theLiterals
> and I get;
> defaultServer defaultShare defaultFolder defaultFile
> \\MyServer\ NULL NULL NULL
> NULL MyShare\ NULL NULL
> NULL NULL MyFolder\ NULL
> NULL NULL NULL MyFile.dat
> but I want it COALESCEd like this;
> defaultServer defaultShare defaultFolder defaultFile
> \\MyServer\ MyShare\ MyFolder\ MyFile.dat
> ...but my syntax is incorrect. Is there an efficient way of doing this.
> I want to have a script/UDF where I can say...
GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')
> and then my one-row recordset will be...
> RS(0) will = '\\MyServer\'
> RS(1) will = 'MyShare\'
> RS(2) will = 'MyFolder\'
> RS(3) will = 'MyFile.dat'
> Thanks for any help!|||How about this:

SELECT TOP 1
defaultServer = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultServer'),
defaultShare = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultShare'),
defaultFolder = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultFolder'),
defaultFile = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultFile')
FROM #theLiterals

That returns the desired record:

\\MyServer\MyShare\MyFolder\MyFile.dat

Or you could create a function that takes 4 parameters like 'defaultServer'
and returns a one-record table populated with the results from those 4
SELECTs.

Jim Geissman|||Missed the beginning of this thread, but if #theLiterals is not trivially
small,
you get an (avg) 2:1 speedup by doing:

SELECT
defaultServer = max(case theKey when 'defaultServer' then theValue
end)
,defaultShare = max(case theKey when 'defaultShare' then theValue
end)
,defaultFolder = max(case theKey when 'defaultFolder' then theValue
end)
,defaultFile = max(case theKey when 'defaultFile' then theValue
end)
FROM #theLiterals

"Jim Geissman" <jim_geissman@.countrywide.com> wrote in message
news:b84bf9dc.0407091511.6338405b@.posting.google.c om...
> How about this:
> SELECT TOP 1
> defaultServer = (SELECT theValue FROM #theLiterals
> WHERE theKey = 'defaultServer'),
> defaultShare = (SELECT theValue FROM #theLiterals
> WHERE theKey = 'defaultShare'),
> defaultFolder = (SELECT theValue FROM #theLiterals
> WHERE theKey = 'defaultFolder'),
> defaultFile = (SELECT theValue FROM #theLiterals
> WHERE theKey = 'defaultFile')
> FROM #theLiterals
> That returns the desired record:
> \\MyServer\ MyShare\ MyFolder\ MyFile.dat
> Or you could create a function that takes 4 parameters like
'defaultServer'
> and returns a one-record table populated with the results from those 4
> SELECTs.
> Jim Geissman|||And first place for minimum reads goes to David Portas!

Thanks everyone for the help. I originally thought doing an aggregate
function
to get rid of NULLS would be inefficient, but by looking at the TRACE
it looks
like it has the most efficient execution plan.

FYI, I listed each of your solutions and the number of reads each
took and some additional questions.

NOTE: The 'theLiterals' table would never be big enough to cause more
than a seconds execution but it is always best to strive for
efficiency anyway. I hope you agree.

-- David Portas
-- 6 reads
-- Warning: Null value is eliminated by an aggregate or other SET
operation.
-- Why is MIN so much faster than MAX?
SELECT
defaultServer = MIN(CASE WHEN theKey = 'defaultServer' THEN theValue
END),
defaultShare = MIN(CASE WHEN theKey = 'defaultShare' THEN theValue
END),
defaultFolder = MIN(CASE WHEN theKey = 'defaultFolder' THEN theValue
END),
defaultFile = MIN(CASE WHEN theKey = 'defaultFile' THEN theValue
END)
FROM theLiterals

-- Mischa Sandberg
-- 18 reads
-- Warning: Null value is eliminated by an aggregate or other SET
operation.
-- Why is MIN so much faster than MAX or is it the way the CASE-WHEN
is
-- formatted?
SELECT
defaultServer = max(case theKey when 'defaultServer' then theValue
end)
,defaultShare = max(case theKey when 'defaultShare' then theValue
end)
,defaultFolder = max(case theKey when 'defaultFolder' then theValue
end)
,defaultFile = max(case theKey when 'defaultFile' then theValue
end)
FROM theLiterals

-- John Bell
-- 24 reads
SELECT
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultServer' ) AS
defaultServer ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultShare' ) AS
defaultShare ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultFolder' ) AS
defaultFolder ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultFile' ) AS
defaultFile

-- John Bell
-- 24 reads
SELECT A.defaultServer, B.defaultShare, C.defaultFolder,
D.defaultFile
FROM
( SELECT theValue AS defaultServer FROM theLiterals WHERE theKey =
'defaultServer' ) A,
( SELECT theValue AS defaultShare FROM theLiterals WHERE theKey =
'defaultShare' ) B,
( SELECT theValue AS defaultFolder FROM theLiterals WHERE theKey =
'defaultFolder' ) C,
( SELECT theValue AS defaultFile FROM theLiterals WHERE theKey =
'defaultFile' ) D

-- Jim Geissman
-- 80 reads
-- Taking off the outside 'FROM theLiterals' returns only the one
record rather
-- than four duplicate records. Therefore the TOP function is then
not needed.
-- So the query becomes the same as John Bell's above with 24 reads
SELECT TOP 1
defaultServer = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultServer'),
defaultShare = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultShare'),
defaultFolder = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultFolder'),
defaultFile = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultFile')
FROM theLiterals

Easier way to convert Non-Unicode to Unicode

I have built a large package and due to database changes (varchar to nvarchar) I need to do a data conversion of all the flat file columns I am bringing in, to a unicode data type. The way I know how to do this is via the data conversion component/task. My question is, I am looking for an easy way to "Do All Columns" and "Map all Columns" without doing every column by hand in both spots.

I need to change all the columns, can I do this in mass? More importantly once I convert all these and connect it to my data source it fails to map converted fields by name. Is there a way when using the data conversion task to still get it to map by name when connecting it to the OLE destination?

I know I can use the wizard to create the base package, but I have already built all the other components, renamed and set the data type and size on all the columns (over 300) and so I don't want to have to re-do all that work. What is the best solution?

In general I would be happy if I could get the post data conversion to map automatically to the source. But because its DataConversion.CustomerID it will not map to CustomerID field on destination. Any suggestions on the best way to do this would save me hours of work...

Thanks.

If SSIS has to use a namespace to identify a column...this happens when you have Source1.CustID and DataConversion1.CustID it can't do the mapping. If you changed the names in the source it could, but that might be more trouble than it's worth.

Wednesday, March 21, 2012

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.
>

Friday, March 9, 2012

Dynamically Changing the length of a varchar(n) field

Hi Everyone,

I've been able to do this in a "primitive" way, simply by identifying
the specific error number in case the value is being truncated, and
then increasing the length of the varchar(n) field by using the ALTER
command, and then duplicating the insert statement, but is there a
standard (shorter) way of doing this?

Here is my code (I'm working in an ASP environment):

<%
var_txt = "abcdefghijklmnopqrstuvwxyz12345678789"

sql = "Insert Into Table1 (text) Values ('" & var_txt & "')"
On Error Resume Next
conn.Execute sql

If err = -2147217833 Then
Response.Write "Error Recognized Successfully! "

sql = "ALTER TABLE Table1 ALTER COLUMN text VARCHAR(" &
Len(var_txt) &
") NOT NULL"
On Error Resume Next
conn.Execute sql

If err<>0 Then
Response.Write "Error while trying to alter Column: " & err & "
= " & err.description & " "
Else
Response.Write "Column altered successfully to: " &
Len(var_txt) &
" "

sql = "Insert Into Table1 (text) Values ('" & var_txt &
"')"
On Error Resume Next
conn.Execute sql

If err<>0 Then
Response.Write " Error number 2: " &
err.description &
" "
Else
Response.Write "Now it was added successfully!
HaHa! "
End If

End If

Else
Response.Write "Success."
End If
%>

Thanks in advance!On 26.12.2006 12:46, John wrote:

Quote:

Originally Posted by

I have a question about dynamically changing the length of a varchar(n)
field, in case the value I'm trying to insert is too big and will give
a "truncated" error, but before the error is given! i.e. Is there some
kind of a way to "test" the length of the field while Inserting the
value into it, and to have it automatically increase its length to the
length of the value being inserted, in case the value is too big?
>
I've been able to do this in a "primitive" way, simply by identifying
the specific error number in case the value is being truncated, and
then increasing the length of the varchar(n) field by using the ALTER
command, and then duplicating the insert statement, but is there a
standard (shorter) way of doing this?

There are several things to say to this. First, the length of a VARCHAR
column should generally be dependent on business requirements - i.e. the
length comes before the inserted values. You can view it as a
requirement (for example a zip code is just 5 characters here in
Germany) and data not satisfying that requirement basically should not
go into that column. In this case the table will only change if the
business requirement changes (they changed length of zip code from 4 to
5 after the wall broke down over here) and not according to data inserted.

Having said that the easiest solution in your case (i.e. if you want to
maintain that you have to insert strings with arbitrary length) is to
just set the length to the max length allowed for that column (in SQL
Server 2k it's 8000 IIRC). Additionally you will have to take
application level measures to limit the length of inserted values to the
columns width.

Alternatively you could use a TEXT column but changing an existing
VARCHAR to a TEXT column is not as easy as executing an ALTER TABLE.

Kind regards

robert|||Hey Robert,

Thank you for your reply! That's pretty good advice and I think I'll do
just that, since it's far less complicated than what I was trying to do
(obviously).

Robert Klemme wrote:

Quote:

Originally Posted by

On 26.12.2006 12:46, John wrote:

Quote:

Originally Posted by

>
There are several things to say to this. First, the length of a VARCHAR
column should generally be dependent on business requirements - i.e. the
length comes before the inserted values. You can view it as a
requirement (for example a zip code is just 5 characters here in
Germany) and data not satisfying that requirement basically should not
go into that column. In this case the table will only change if the
business requirement changes (they changed length of zip code from 4 to
5 after the wall broke down over here) and not according to data inserted.
>
Having said that the easiest solution in your case (i.e. if you want to
maintain that you have to insert strings with arbitrary length) is to
just set the length to the max length allowed for that column (in SQL
Server 2k it's 8000 IIRC). Additionally you will have to take
application level measures to limit the length of inserted values to the
columns width.
>
Alternatively you could use a TEXT column but changing an existing
VARCHAR to a TEXT column is not as easy as executing an ALTER TABLE.
>
Kind regards
>
robert

Wednesday, March 7, 2012

dynamic where prob

hi!

i can't seem to get this right...

create procedure PRstudentInfo

(

@.pStudentCode varchar(20) = NULL,

@.pHidden bit

)

begin

declare @.select varchar(max), @.where varchar(max)

set @.select =

'select b.name, c.studentcode from basicinfo b inner join fullinfo c on b.code = c.studentcode'

set @.where =

'WHERE isnull(c.studentcode,'''') ' + 'like ' + '''%' + isnull(@.pStudentCode,'') + '%''' + ' and ' +

' isnull(c.hidden,'''') = ''' + isnull(@.pHidden,'') '' --> here is the error

exec(@.select + @.where)

end

the error message is :

Incorrect syntax near ''

i also tried this:

' isnull(c.confidential,'''') = ''' + isnull(@.pConfidential,'') + ' '

The data types varchar and bit are incompatible in the add operator.

A tip : add the following code line:

print @.select + @.where , to see how sql string is made.

I suppose you have to put a space :

'hereWHERE isnull(c.studentcode,'''') etc

|||

Change it to this:

' isnull(c.hidden,'''') = ''' + cast(isnull(@.pHidden,'') as char(1))

|||WOuld be nice if you can describe the next time, what you are trying to do within your code.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Sunday, February 26, 2012

dynamic use of stored procedure resultset

Hello,

I have a stored procedur like this:

--------------
ALTER PROCEDURE dbo.pdpd_DynamicCall
@.SQLString varchar(4096) = null
AS

create TABLE #T1
( column_1 varchar(10) ,
column_2 varchar(100) )

insert into #T1
execute ('execute ' + @.SQLString )

select * from #T1
--------------

The problem is that I want to call different procedures that can give back different columns.
Therefor I would have to define the table #T1 generically.But I don't know how.
Can anyone help me on this problem?

thank you
Werneryou do not need the creation of the temporary table.

ALTER PROCEDURE dbo.pdpd_DynamicCall
@.SQLString varchar(4096) = null
AS

execute ('execute ' + @.SQLString )

this solves your problem but this is the most useless SP ever and your going to have stuff like cached execution plans that do not match the query you are executing.|||This sproc is very dangerous. Whoever has rights to execute it has right to execute arbitrary sql scripts, such as "truncate table AllMyCustomers". Do you really want that?

You are asking for trouble if you put this in a production system.|||Hello,

I have a stored procedur like this:

--------------
ALTER PROCEDURE dbo.pdpd_DynamicCall
@.SQLString varchar(4096) = null
AS

create TABLE #T1
( column_1 varchar(10) ,
column_2 varchar(100) )

insert into #T1
execute ('execute ' + @.SQLString )

select * from #T1
--------------

The problem is that I want to call different procedures that can give back different columns.
Therefor I would have to define the table #T1 generically.But I don't know how.
Can anyone help me on this problem?

thank you
Werner
Like jezemine says - dynamic SQL needs to be encapsulated in a very controlled fashion.

I have a similar situation where our "configurator" actually has column names to define a mapping between Inventory and Sales. Long story.

We offer the column names on a drop-down list.

The Stored Procedure that does the implementation is passed column names in various positions. Those column names are a result of a drop-down box (so the can't just formulate their own SQL scripting), and they are of limited size (ie: only big enough for a reasonable column name - like 40 characters).

For your case; if you had a list of column names, types, and sizes rather than the full SQL statement, you could use them to build your temp table.

I have never tried passing an array (or collection) as an SP_ parameter, but that would be ideal if you have an unknown number of columns.

You could also first do some parsing verify that they are valid column names (no spaces or punctuation) to further ensure they aren't passing in DLL commands like "truncate table ...".

So your Stored Procedure would supply all SQL keywords and restrict any from being passed.|||Thank you all for the detailled help!!
Especially the security aspect is a part I have to rethink.

best regards
Werner

Friday, February 24, 2012

Dynamic table name from varchar field

Hi,
How can i execute folowing T-SQL properly ?
Error given due to so.name is a varchar value.
Select Distinct so.name as TableName,(Select count(*) from so.name) as
RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
so.xtype='U'
The output will be "
TableName RecCount
-- -- --DMP wrote:
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id
> where so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
Erland covers this here:
http://www.sommarskog.se/dynamic_sql.html
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You can't execute dynamic SQL inline like that, read up on EXECUTE()
fortunately a rowcount is available in sysindexes that you can use without
traversing each table anyway:
SELECT SysObjects.Name,
SysIndexes.Rows
FROM SysObjects
JOIN SysIndexes ON SysIndexes.ID=SysObjects.ID AND SysIndexes.IndID IN
(0,1)
WHERE SysObjects.xtype='U'
for reference IndID in (0,1) eliminates all indexes but the base tables
0=heaped, 1=clustered. Note that queries on the system tables are likely to
fail if you upgrade to a new version of SQL.
Mr Tea
http://mr-tea.blogspot.com
"DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
news:eEI%236wTAFHA.1084@.tk2msftngp13.phx.gbl...
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
> so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
>

dynamic table name (was "Please help ! SQL")

Hi--

I am new to T-SQL i got this code from some website but its not working can anyone let me know why
@.cur_tab_name varchar(30) is not decleared while i have decleared
I want to use this for dynamic name of the table.

Thanks.

create table temp_tab
(
tab_name varchar(30),
no_of_rows INTEGER,
)

DECLARE
curREVIEW

CURSOR FOR
select name
from sysobjects
where xtype = 'U'

DECLARE @.cur_tab_name varchar(30)

OPEN curREVIEW

FETCH curREVIEW INTO @.cur_tab_name

WHILE (@.@.FETCH_STATUS =0)
BEGIN
DECLARE @.count integer

select @.count = count(*) from @.cur_tab_name
INSERT INTO temp_tab
(@.cur_tab_name, @.count)

FETCH curREVIEW INTO @.cur_tab_name

END

CLOSE curREVIEW

DEALLOCATE curREVIEWDynamic SQL must be passed as a string to EXEC:

EXEC ('select @.count = count(*) from ' + @.cur_tab_name)

...but this still will not give you what you want, because the code will EXECute in a different scope than your @.count variable.

Try this:

Declare @.SQLString varchar(500)
Set @.SQLString = 'INSERT INTO temp_tab select ''' + @.cur_tab_name + ''', count(*) from ' + @.cur_tab_name
EXEC (@.SQLString)

Friday, February 17, 2012

Dynamic SQL Question

I have to get a count of records using dynamic sql.
I have the following and I'm getting errors (Syntax error converting
the varchar value 'SELECT ' to a column of data type int.):
DECLARE @.sCalldate varchar(10)
DECLARE @.SQL varchar(8000)
DECLARE @.RC int
SELECT @.sCalldate ='20050712'
SELECT @.sEmployeeNameId = '0'
SELECT @.sAgentid ='0'
SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = ' + @.sCallDate +
' AND ISNULL(sRawLogout, ''x'') = ''x'''
EXEC (@.SQL)
How can I make this work?
Thanks,
Ninelhi ninel,
Please, post DDL instructions but at first, try to sustitute this line for
the another one:

> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@.SQL)

> set @.sql = ('
SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''')

> EXEC (@.SQL)
"ninel" wrote:

> I have to get a count of records using dynamic sql.
> I have the following and I'm getting errors (Syntax error converting
> the varchar value 'SELECT ' to a column of data type int.):
> DECLARE @.sCalldate varchar(10)
> DECLARE @.SQL varchar(8000)
> DECLARE @.RC int
> SELECT @.sCalldate ='20050712'
> SELECT @.sEmployeeNameId = '0'
> SELECT @.sAgentid ='0'
> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@.SQL)
> How can I make this work?
> Thanks,
> Ninel
>|||I see no need for dynamic SQL here:
DECLARE @.sCalldate varchar(10)
DECLARE @.RC int
SELECT @.sCalldate ='20050712'
SELECT @.RC=Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = @.sCallDate
AND ISNULL(sRawLogout, 'x') = 'x'
Razvan|||Hi!
declare
@.sql varchar(8000),
@.sCalidate varchar(10),
@.RC int;
set @.sql = 'select @.RC = count(*) from from tmPunchTime where scalldate =
@.sCallDate and is null(sRawLogout, ''x'') = ''x''';
exec sp_executesql @.sql, N'@.RC int out, @.sCalidate varchar(10)', @.RS out,
@.sCalidate = @.sCalidate;
select @.RC
Micle.
"ninel" <ngorbunov@.onetouchdirect-dot-com.no-spam.invalid> wrote in message
news:orOdnbUkTPNtF33fRVn_vA@.giganews.com...
>I have to get a count of records using dynamic sql.
> I have the following and I'm getting errors (Syntax error converting
> the varchar value 'SELECT ' to a column of data type int.):
> DECLARE @.sCalldate varchar(10)
> DECLARE @.SQL varchar(8000)
> DECLARE @.RC int
> SELECT @.sCalldate ='20050712'
> SELECT @.sEmployeeNameId = '0'
> SELECT @.sAgentid ='0'
> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@.SQL)
> How can I make this work?
> Thanks,
> Ninel
>|||Hi Ninel,
I agree with Razvan Socol that there is no need for Dynamic SQL.
Please visit this link and view When not to use Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html ( An authority on Dynamic
SQL)
If in your case you donot know TableName or Parameters are changing
then Micle approach is what you need
Please let me know if it clear your doubts.
With warm regards
Jatinder|||No need to use dynmaic sql to get the results you want. Please see example
below.
DECLARE @.sCalldate varchar(10)
DECLARE @.SQL varchar(8000)
DECLARE @.RC int
DECLARE @.sEmployeeNameId int
DECLARE @.sAgentid int
SELECT @.sCalldate ='20050712'
SELECT @.sEmployeeNameId = '0'
SELECT @.sAgentid ='0'
CREATE TABLE #tmPunchTime
(
Scalldate datetime,
sRawLogout sysname
)
Insert #tmPunchTime
Values ('20050712','x')
Insert #tmPunchTime
Values ('20050712','x')
Insert #tmPunchTime
Values ('20050712','x')
SELECT @.RC =(SELECT Count(*)
FROM #tmPunchTime
WHERE convert(nvarchar,scalldate,112) = @.sCallDate
AND ISNULL(sRawLogout, 'x') = 'x')
SELECT @.RC
DROP TABLE #tmPunchTime
"ninel" wrote:

> I have to get a count of records using dynamic sql.
> I have the following and I'm getting errors (Syntax error converting
> the varchar value 'SELECT ' to a column of data type int.):
> DECLARE @.sCalldate varchar(10)
> DECLARE @.SQL varchar(8000)
> DECLARE @.RC int
> SELECT @.sCalldate ='20050712'
> SELECT @.sEmployeeNameId = '0'
> SELECT @.sAgentid ='0'
> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@.SQL)
> How can I make this work?
> Thanks,
> Ninel
>

Dynamic SQL Problem

I'm getting "Invalid Column" error with below code. Can anyone Help?
CODE:
USE [Northwind]
GO
declare @.SQL varchar(1000), @.debug int
declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
int
set @.sTable="Orders"
set @.sField="OrderDate"
set @.sField2="employeeID"
set @.employeeID = 3
set @.debug = 1
SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
CAST(@.employeeID AS VARCHAR(55))
IF @.debug = 1
PRINT @.sql
--EXEC(@.SQL)Your @.variables are not passed into the Exec(), nor should they be. The
Exec() is run separately from the stored proceedure.
Here is a simple example of how I circumvent this:
declare @.SQL varchar(1000)
declare @.getTable Char(40), @.getField Char(40), @.getFilter varchar(100)
set @.getTable='Orders'
set @.getField='OrderDate'
set @.getFilter='employeeID = 3'
SET @.SQL = 'SELECT Max([getField]) FROM getTable WHERE getFilter'
Set @.SQL = Replace(@.SQL,'getField',@.getField)
Set @.SQL = Replace(@.SQL,'getTable',@.getTable)
Set @.SQL = Replace(@.SQL,'getFilter',@.getFilter)
EXEC(@.SQL)
"Scott" wrote:

> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||that's fine except i need a @.employeeID variable. i hardcoded 3 just for
this simple example and will actually be passing 2 WHERE variables in the
production code. can you modify your code?
"John Cappelletti" <JohnCappelletti@.discussions.microsoft.com> wrote in
message news:145010B5-D41D-4184-BE78-7F239164BD44@.microsoft.com...
> Your @.variables are not passed into the Exec(), nor should they be. The
> Exec() is run separately from the stored proceedure.
> Here is a simple example of how I circumvent this:
> declare @.SQL varchar(1000)
> declare @.getTable Char(40), @.getField Char(40), @.getFilter varchar(100)
> set @.getTable='Orders'
> set @.getField='OrderDate'
> set @.getFilter='employeeID = 3'
> SET @.SQL = 'SELECT Max([getField]) FROM getTable WHERE getFilter'
> Set @.SQL = Replace(@.SQL,'getField',@.getField)
> Set @.SQL = Replace(@.SQL,'getTable',@.getTable)
> Set @.SQL = Replace(@.SQL,'getFilter',@.getFilter)
> EXEC(@.SQL)
>
> "Scott" wrote:
>|||My goof. Your doing pretty much what I am. However, you have double quotes
around you @.variables.
Also watch out for empty space, by declaring as char rather than varchar,
your string is longer than it needs to be.
"Scott" wrote:

> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||You main problem is that you delimited your strings with double quote marks.
In T-SQL, strings are delimited with single quotes. i.e. set @.sTable='Order
s'
For readability of the print @.sql, I changed your variables from char(40) to
varchar(40). The code will execute fine with char(40), it just has a lot of
extra spaces.
If you change the variables to varchar or you happen to have a table name of
40 characters, the WHERE clause will fail because there will be no space
between them. I suggest you put a space in front of the word WHERE as I've
done below.
Finally, EXEC (@.SQL) is no longer the recommended best practices. You
should be using exex sp_executesql which requires unicode input so I changed
@.SQL from varchar(1000) to nvarchar(1000).
Hope that helps,
Joe
Here's the corrected code:
USE [Northwind]
GO
declare @.SQL nvarchar(1000), @.debug int
declare @.sTable varchar(40), @.sField varchar(40), @.sField2 varchar(40),
@.employeeID int
set @.sTable='Orders'
set @.sField='OrderDate'
set @.sField2='employeeID'
set @.employeeID = 3
set @.debug = 1
SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
SET @.SQL = @.SQL + ' WHERE ' + @.sTable + '.' + @.sField2 + '=' +
CAST(@.employeeID AS VARCHAR(55))
IF @.debug = 1
PRINT @.sql
exec sp_executesql @.sql
"Scott" wrote:

> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||Parameterised dynamic queries are best done using the sp_executesql system
procedure. No hassle, no fuss - pure execution.
ML
http://milambda.blogspot.com/

Dynamic SQL is faster.

Hi I have a stored proc -
takes a long time to execute so re-compilation isn't an issue.
I pass a comma separated varchar param to the stored procedure.
exec sp_somename @.CSV = '1,2,3'
If I build the SQL statement in the SP and then execute the statement
dynamically:
EXEC ('SELECT * FROM table1, ... other tables WHERE ID IN (1,2,3,4) ...rest
of sql'
as opposed to this,
having populated a temp table #t with @.CSV values.
SELECT * FROM
table1, #t, ... other tables
WHERE
table1.ID = #t.ID
...rest of sql
I used a function and SP to populate the #t; all took negliable speed.
The dynamic SQL performs twice as fast as the temp table/variable method.
Surprised I thought SELECT IN (.....) was converted into a join and would
be just as fast
Don't like Dynamic SQL if I can avoid it.Do you have any indexes on the temp table? if not, would one help on the
joined column?
David Gugick
Imceda Software
www.imceda.com
"Yitzak" <terryshamir@.bob.com> wrote in message
news:prG2e.903$VN1.310@.newsfe1-win.ntli.net...
> Hi I have a stored proc -
> takes a long time to execute so re-compilation isn't an issue.
> I pass a comma separated varchar param to the stored procedure.
> exec sp_somename @.CSV = '1,2,3'
>
> If I build the SQL statement in the SP and then execute the statement
> dynamically:
> EXEC ('SELECT * FROM table1, ... other tables WHERE ID IN (1,2,3,4)
> ...rest
> of sql'
> as opposed to this,
> having populated a temp table #t with @.CSV values.
> SELECT * FROM
> table1, #t, ... other tables
> WHERE
> table1.ID = #t.ID
> ...rest of sql
> I used a function and SP to populate the #t; all took negliable speed.
> The dynamic SQL performs twice as fast as the temp table/variable method.
> Surprised I thought SELECT IN (.....) was converted into a join and would
> be just as fast
> Don't like Dynamic SQL if I can avoid it.
>
>|||I thought because the table was so small at most having 10 rows - they would
be no benefit but I'll give it a go.
Thanks
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#DuCivYNFHA.3192@.TK2MSFTNGP10.phx.gbl...
> Do you have any indexes on the temp table? if not, would one help on the
> joined column?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
> "Yitzak" <terryshamir@.bob.com> wrote in message
> news:prG2e.903$VN1.310@.newsfe1-win.ntli.net...
method.
would
>|||Put some indexes on and did improve performance.
Changed some other stored procedures but still Dynamic SQL wins out. Thats
not to mention its clear advantage when you can easily optimise for no ids
e.g. @.param = '' by not including the IN statement in the SQL string
executed.
Just don't like Dynamic SQL - sp_depnds wont work. All the security issues.
but looks like I gotta use it.|||>> I pass a comma separated varchar param to the stored procedure. <<
Ever hear about First Normal Form (1NF)?
Pardon the fact that this "cut & paste" is in Standard SQL and not
dialect. Passing a list of parmeters to a stored procedure can be done
by putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:
CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);
INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
..
This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.
CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL PRIMARY KEY,
parm INTEGER NOT NULL);
It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a
standard SQL programming trick, Now, the real query, in SQL-92 syntax:
INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING ( ',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;
The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma.
You can then write:
SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);
Of course the right way to do this would be with a base table that
holds the list, or a longer parameter list:
WHERE x IN (@.p1, COALESCE (@.p2, @.p1), .. COALESCE (@.p99, @.p1);
The reason for the Coalesce() is to guarantee you have no nulls. You
must have a value for @.p1. All the other partameters default to NULL.
Alternatively, you can have a local variable, @.p0, which is set to some
impossible value as a sentinel.|||Actually I executed
set @.param = '1,2,3'
insert into #t
exec('SELECT ' + REPLACE(@.param, ',' ' UNION ALL SELECT ') )
To give me
insert into #t
exec (select 1 union all select 2 union all select 3)
Each row produced by executing this is one row and column of the temp table.
Is that what you meant by 1NF (every field must be atomic?)
Must of explained myself badly.
Point is using this CSV param dynamically in a SP is much faster than
breaking CSV param down into a temp table/variable or using a table
returning function.
SP recompile time is not an issue. Maybe if the CSV param gets very large
then using the temp table or function may be quicker.
Point is don't like dynamic SQL but this time forced to.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112318927.848861.167290@.g14g2000cwa.googlegroups.com...
> Ever hear about First Normal Form (1NF)?
> Pardon the fact that this "cut & paste" is in Standard SQL and not
> dialect. Passing a list of parmeters to a stored procedure can be done
> by putting them into a string with a separator. I like to use the
> traditional comma. Let's assume that you have a whole table full of
> such parameter lists:
> CREATE TABLE InputStrings
> (keycol CHAR(10) NOT NULL PRIMARY KEY,
> input_string VARCHAR(255) NOT NULL);
> INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
> INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
> ...
> This will be the table that gets the outputs, in the form of the
> original key column and one parameter per row.
> CREATE TABLE Parmlist
> (keycol CHAR(10) NOT NULL PRIMARY KEY,
> parm INTEGER NOT NULL);
> It makes life easier if the lists in the input strings start and end
> with a comma. You will need a table of sequential numbers -- a
> standard SQL programming trick, Now, the real query, in SQL-92 syntax:
>
> INSERT INTO ParmList (keycol, parm)
> SELECT keycol,
> CAST (SUBSTRING (I1.input_string
> FROM S1.seq
> FOR MIN(S2.seq) - S1.seq -1)
> AS INTEGER)
> FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
> WHERE SUBSTRING ( ',' || I1.input_string || ',' FROM S1.seq FOR 1) =
> ','
> AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
> ','
> AND S1.seq < S2.seq
> GROUP BY I1.keycol, I1.input_string, S1.seq;
> The S1 and S2 copies of Sequence are used to locate bracketing pairs of
> commas, and the entire set of substrings located between them is
> extracted and cast as integers in one non-procedural step. The trick
> is to be sure that the right hand comma of the bracketing pair is the
> closest one to the first comma.
> You can then write:
> SELECT *
> FROM Foobar
> WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);
> Of course the right way to do this would be with a base table that
> holds the list, or a longer parameter list:
> WHERE x IN (@.p1, COALESCE (@.p2, @.p1), .. COALESCE (@.p99, @.p1);
> The reason for the Coalesce() is to guarantee you have no nulls. You
> must have a value for @.p1. All the other partameters default to NULL.
> Alternatively, you can have a local variable, @.p0, which is set to some
> impossible value as a sentinel.
>|||Yitzak wrote:
> Hi I have a stored proc -
> takes a long time to execute so re-compilation isn't an issue.
> I pass a comma separated varchar param to the stored procedure.
> exec sp_somename @.CSV = '1,2,3'
>
> If I build the SQL statement in the SP and then execute the statement
> dynamically:
> EXEC ('SELECT * FROM table1, ... other tables WHERE ID IN (1,2,3,4)
> ...rest of sql'
>
You have several options, which are explained at
http://www.sommarskog.se/arrays-in-sql.html
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thanks
Tried different options all better than dynamic SQL (maintenance,
debugging, security) - essentially all use a table to join.
This in my cases is always double the speed of the similar Dynamic SQL.
Recompilation ain't an issue on a SP that takes 40secs to run..
Can't understand as I thought MSSQL turned a "Select IN" into a join on temp
table, but creating my own indexed temp table/variable from SP or Function
takes twice teh time of "SELECT IN"
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:#EOF$xwNFHA.2252@.TK2MSFTNGP15.phx.gbl...
> Yitzak wrote:
> You have several options, which are explained at
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Wednesday, February 15, 2012

dynamic sql in Cursor

a table named tbl_customerMaster

create table(customerID int, customerName varChar(50), Adderss varChar(100))

create procedure sp_saTest ( @.customerID varChar(20) )

declare curCustomer cursor

for SELECT customerName, Adderss FROM tbl_customerMaster WHERE customerID IN (@.customerID)

open curCustomer

fatch....

....

xyz, xyz, close cursor

the sp created successfully, but when i try to execute that sp i found a error there

Exec sp_saTest (1,2,5,7,10)

Server: Msg 245, Level 16, State 1, Procedure sp_Customer
Syntax error converting the varchar value '1,2,5,7,10' to a column of data type int.

what is the problem there?

suppose when i fire as follow

SELECT customerName, Adderss FROM tbl_customerMaster WHERE customerID IN (1,2,5,7,10)

directly in it will be work properly, then why there is error with parameter name?

i m using SQL Server 2000.

The problem is that you're passing in a string, when it wants a list of numbers. I know the string happens to look like a list of numbers, but it's actually not one.

You could do something like:

WHERE ',' + @.customerID + ',' like '%,' + cast(customerID as varchar(20)) + ',%'

But this is kinda nasty. Another alternative is to split the string into a series of numbers. There is code for this at
http://www.sql.co.il/books/insidetsql2005/source_code/TechEd%202006%20Israel%20-%20Advanced%20T-SQL%20Techniques.txt

It's down near there end, an example of just this type of behaviour.

Hope this helps...

Rob

Dynamic SQL in cursor

I need to pass a list of values into a cursor as such...

DECLARE
@.group_SQL varchar(255)

SET @.group_SQL = 'SELECT group_id FROM groups where group_id in (' + @.group_id + ')'

DECLARE groupContact_import_cursor CURSOR
FOR EXEC(@.group_SQL)
OPEN groupContact_import_cursor
FETCH NEXT FROM groupContact_import_cursor INTO @.group_id
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
insert into groupContacts (group_id, contact_id) values (@.group_id, @.new_cid)
FETCH NEXT FROM groupContact_import_cursor INTO @.group_id
END
CLOSE groupContact_import_cursor
DEALLOCATE groupContact_import_cursor

But MS SQL doesn't seem to like the FOR EXEC(@.group_SQL). Can someone shed some light?

TIAI think it should work like this.

DECLARE groupContact_import_cursor CURSOR
FOR SELECT group_id FROM groups where group_id = @.group_id|||Why? A contact can be assigned to many group_id values, not just one.|||SET @.group_SQL = 'SELECT group_id FROM groups where group_id in (' + @.group_id + ')'

set @.group_SQL = 'DECLARE groupContact_import_cursor CURSOR FOR ' + @.group_SQL

exec (@.group_SQL)
OPEN groupContact_import_cursor
...|||bingo, that did it. I didn't know I needed to include the DECLARE portion, oh well...

Thanks a lot.|||bingo, that did it. I didn't know I needed to include the DECLARE portion, oh well...

Thanks a lot.

It did?

I'm highly sceptical...

What does @.group_id look like

Dynamic SQL Help needed.

I have the following stored procedure :

CREATE PROCEDURE usp_CraneRental_UpdateRate
@.iEntryID int,
@.cField sysname,
@.cValue varchar(100),
@.dtModified datetime OUTPUT

AS

SELECT @.dtModified = GetDate()

DECLARE @.cSql VARCHAR(500)

SELECT @.cSql = 'UPDATE tbCraneRentalRates SET ' + @.cField + ' = ' + RTRIM(@.cValue) +
', Modified = ' + '"' + CAST(@.dtModified AS VARCHAR) + '"' +
' WHERE EntryID = ' + CAST(@.iEntryID AS VARCHAR)

EXEC(@.cSql)
IF(@.@.ERROR <> 0 OR @.@.ROWCOUNT <= 0)
RAISERROR('Failed to update transportation rate!',16,1)
GO

that generates the following SQL String :

UPDATE tbCraneRentalRates SET MoveIn = 0, Modified = "May 20 2004 9:59 AM" WHERE EntryID = 1

The error I am getting is:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'May 20 2004 9:59AM'.

???
This doesn't make sence, the SQL statement looks perfectly fine ?

Any help?
Mike Btry using 2 ' quotes to make it create ', youre giving it the text in speach " " marks which sql doesn't like.

try '' should make it display '|||put this before the create statement in qa:

set quoted_identifiers off|||try using 2 ' quotes to make it create ', youre giving it the text in speach " " marks which sql doesn't like.

try '' should make it display '

That works, thanks.

Mike B

Thursday, March 29, 2012

Easy way to index a column with URLs

Easy way to index a column with URLs

Easy way to index a column with URLs

Thursday, March 22, 2012

Easy BCP question ;)

easily editting fields with more than 1024 characters

Easiest way of combining multiple fields from different records into one record?

Easier way to convert Non-Unicode to Unicode

Wednesday, March 21, 2012

Dynamically use variables in SQL in EXECUTE

Friday, March 9, 2012

Dynamically Changing the length of a varchar(n) field

Wednesday, March 7, 2012

dynamic where prob

Sunday, February 26, 2012

dynamic use of stored procedure resultset

Friday, February 24, 2012

Dynamic table name from varchar field

dynamic table name (was "Please help ! SQL")

Friday, February 17, 2012

Dynamic SQL Question

Dynamic SQL Problem

Dynamic SQL is faster.

Wednesday, February 15, 2012

dynamic sql in Cursor

Dynamic SQL in cursor

Dynamic SQL Help needed.

Dynamic SQL Command

Blog Archive

About Me