Passing multiple values in 1 varchar variable in a stored proc IN

This is a common Newbie question. This shows that you don't know SQL
uses only scalar parameters and has only one data structure, the table.
This is a fundamental programming concept that you should learn in the
first w of any SQL language class. SQL is not your original
procedural programming language.
1) The dangerous, slow kludge is to use dynamic SQL and admit that any
random furure user is a better programmer than you are. It is used by
Newbies who do not understand SQL or even what a compiled language is.
A string is a string; it is a scalar value like any other parameter; it
is not code. Again, this is not just an SQL problem; this is a basic
misunderstanding of programming principles.
2) 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');
etc.
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,
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 query, in SQL-92 syntax
(translate into your local dialect):
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 add a computation for the
relative postion of each element in the list (left as a exercise for
the student)
You can then write:a query like this:
SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE parm IS NOT NULL);
Hey, I can write kludges with the best of them, but I don't. You need
to at the very least write a routine to clean out blanks and
non-numerics in the strings, take care of floating point and decimal
notation, etc. Basically, you must write part of a compiler in SQL.
Yeeeech! Or decide that you do not want to have data integrity, which
is what most Newbies do in practice altho they do not know it.
3) The right way is to use tables with the IN () predicate, You set up
the procedure declaration with a "fake array" made from a repeated
gorup, like this in SQL/PSM (translate into your local dialect):
CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm -- kill redundant dups
FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
WHERE parm IS NOT NULL -- ignore empty aparameters
AND <other conditions> )
AND <more predicates>;
<more code>;
END;
The idea is that creating a derived table will perform better .You can
also add functions to the parameters like UPPER(pi), apply CASE
expressions like in T-SQL
(CASE WHEN @.p1 = 'usa' THEN @.p2 ELSE 2.2 * @.p2 END)
or use scalar subqueries like this on subsets of the parameters:
(SELECT L.address_code
FROM Locations AS L
WHERE @.p1 = L.longitude
AND @.p2 = L.latitude
AND @.p3 = 'Paris');
SQL Server can have up to 1,024 parameters in a stored procedure and
that is usually good enough. If not, make two calls to the procedure
...> This is a common Newbie question. This shows that you don't know SQL
> uses only scalar parameters and has only one data structure, the table.
> This is a fundamental programming concept that you should learn in the
> first w of any SQL language class. SQL is not your original
> procedural programming language.
This shows your lack of industrial programming experience and exposure, this
is a common requirement from application screeens that allow multiple
values, for instance a multi-value select list.

> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter; it
> is not code. Again, this is not just an SQL problem; this is a basic
> misunderstanding of programming principles.
Why is it dangerous?
Why is it slow?
Why is it a kludge?
Dynamic SQL is not used by newbies, its used by people who understand how to
get an effiecent well maintained and scalable solution.
You repeated 'can' this answer and people repeatedly ask you (including
myself) to post your statistics backing up your claims - you never do which
we can only conclude that you are talking rubbish.
Your proposed solution is slow and will not scale and is certainly
significantly slower and more combersome than do it the correct way - using
dynamic SQL or XML instead.
You need to stop and do a fundemental programming course, go get some real
industrial experience instead of gaining experience from books and playing
with the product.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1135719723.547696.113660@.g14g2000cwa.googlegroups.com...
> This is a common Newbie question. This shows that you don't know SQL
> uses only scalar parameters and has only one data structure, the table.
> This is a fundamental programming concept that you should learn in the
> first w of any SQL language class. SQL is not your original
> procedural programming language.
> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter; it
> is not code. Again, this is not just an SQL problem; this is a basic
> misunderstanding of programming principles.
> 2) 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');
> etc.
> 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,
> 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 query, in SQL-92 syntax
> (translate into your local dialect):
> 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 add a computation for the
> relative postion of each element in the list (left as a exercise for
> the student)
> You can then write:a query like this:
> SELECT *
> FROM Foobar
> WHERE x IN (SELECT parm FROM Parmlist WHERE parm IS NOT NULL);
> Hey, I can write kludges with the best of them, but I don't. You need
> to at the very least write a routine to clean out blanks and
> non-numerics in the strings, take care of floating point and decimal
> notation, etc. Basically, you must write part of a compiler in SQL.
> Yeeeech! Or decide that you do not want to have data integrity, which
> is what most Newbies do in practice altho they do not know it.
> 3) The right way is to use tables with the IN () predicate, You set up
> the procedure declaration with a "fake array" made from a repeated
> gorup, like this in SQL/PSM (translate into your local dialect):
> CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
> INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
> BEGIN
> SELECT foo, bar, blah, yadda, ...
> FROM Floob
> WHERE my_col
> IN (SELECT DISTINCT parm -- kill redundant dups
> FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
> WHERE parm IS NOT NULL -- ignore empty aparameters
> AND <other conditions> )
> AND <more predicates>;
> <more code>;
> END;
> The idea is that creating a derived table will perform better .You can
> also add functions to the parameters like UPPER(pi), apply CASE
> expressions like in T-SQL
> (CASE WHEN @.p1 = 'usa' THEN @.p2 ELSE 2.2 * @.p2 END)
> or use scalar subqueries like this on subsets of the parameters:
> (SELECT L.address_code
> FROM Locations AS L
> WHERE @.p1 = L.longitude
> AND @.p2 = L.latitude
> AND @.p3 = 'Paris');
> SQL Server can have up to 1,024 parameters in a stored procedure and
> that is usually good enough. If not, make two calls to the procedure
> ...
>|||Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> Dynamic SQL is not used by newbies, its used by people who understand
> how to get an effiecent well maintained and scalable solution.
But for this particular problem, dynamic SQL is definitely not very
scalable. When the list grows in size, the performance for IN() gets
horrendeous, at least in SQL 2000. (I have not checked whether SQL 2005
has any improvements.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That depends on the number of elements for the IN clause, there are only 3
on the OP's post so I don't see a problem, and the problem isn't anything to
do with dynamic SQL, rather, the way the IN clause works.
For a larger IN list, say hundreds rather than < dozen then i would then I'd
probably chop the list up into a set and do an IN or EXISTS.
But to repeat for the benefit of celko, this is not a dynamic sql
performance problem but rather the number of elements on the IN clause.
Tony.
Tony Rogersonen
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973BE48CB0Yazorman@.127.0.0.1...
> Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> But for this particular problem, dynamic SQL is definitely not very
> scalable. When the list grows in size, the performance for IN() gets
> horrendeous, at least in SQL 2000. (I have not checked whether SQL 2005
> has any improvements.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Passing long text strings to a stored procedure

Hello!

I am attempting to insert a group of records into a SQL Server 2000 database table. The data for each of these records is the same, with the exception of a foreign key (hereafter known as the 'RepKey') and the generated primary key. To improve performance and cut down on the network traffic, I pack the RepKeys in a comma-delimited string and send it as a single parameter, with the intention of parsing it in the stored proicedure to obtain each individual RepKey, or to use it as a list in an 'WHERE RepKey IN (' + @.RepKeyString + ')' type of query.

My problem is that there may be 1000's of items in this string. Using a varchar(8000) as the parameter type is too short, while using the 'text' data type does not allow me to perform any string operations on it. Any ideas on how to make one network call and insert multiple records that breaks the 8000 character barrier?

One thing that I cannot do is add a table so that the record is stored once, then mapped to each individual rep key. The database structure cannot change. Other solutions that I may not have considered are welcome. Thanks!Can you add a global temp table?
You could write all the data to a disk file then bcp it in to the global table.
Could also create a disconnected recordset on the golobal temp table, diconnect it, populate it then connect it to commit the records then use that.

You could use a text datatype then use substring to parse it in chunks and use char functions on it.|||Nigelrivett idea of a text file sounds interesting. What if the parameter used in your stored procedure was the path to a file containing the list of RepKeys? Once in your procedure you use BULK INSERT into a temporay table (Globle table if needed like nigelrivett suggested) then perform the same looping as you would have done before.

sp_MyProc (RepKeyFile AS varchar(50), ....)

CREATE TABLE #temptable ...
BULK INSERT #temptable FROM @.RepKeyFile
.
.
.
CREATE CURSOR on #temptable
loop through

The only problem is your point on:
or to use it as a list in an 'WHERE RepKey IN (' + @.RepKeyString + ')' type of query.

I thought that you could create a local text variable and while looping append the RepKey to the local text field, SET @.txt = @.txt + ',' + @.RepKey. However I got an error when trying to create a local variable as type text.

Msg 2739, Level 16, State 1, Server ATLAS, Line 1
The text, ntext, and image data types are invalid for local variables.|||Thanks guys - I ended up using the substring procedure to break off chunks, then used an INSERT..SELECT statement that looks like the following:

SET @.query = "INSERT INTO RepContact([fields])"
SET @.query = @.query + "SELECT RepKey, [@.vars] FROM Rep WHERE RepKey IN (" + @.currentString + ")"
exec(@.query)

@.CurrentString is the current list of keys. Each time through the loop, as long as there are still items, the query is run.

Thanks again - if anyone has any ideas on speeding this up, it would be greatly appreciated. (The insert runs a bit slower than I would have hoped).

Everett|||I was thinking that the text file would hold the keys delimitted by crlf so that the bcp would insert them into separate rows and you wouldn't have to do any further processing.|||I think that I would prefer to leave it as it is and avoid writing to and reading from disk. Wouldn't this make it slower, not faster? Anyways, thanks again.|||>> Wouldn't this make it slower, not faster?

Depends on the data and environment.
the bcp will be non-logged so the inser will be faster. It will reduce the handshaking across the network and reduce the amount of manipulation needed before the insert into the production tables.
It would probably end up slower but maybe not. It does give an automatic record of the dta inserted from the text files and makes it easy to make the insert asynchronous if you need to.|||I'll try it during the week and advise you of the outcome.

Thanks again for all of your help.

Passing IN() values as parameter

Hi...

I'm creating a procedure to fetch rows from table. One field will come come as IN(). Its the condition. That field is numeric field (note down), i would like to pass the In values as parameter.

eg: procedurename @.fieldvalue varchar(100)
as
begin
...

WHERE fieldname IN(@.fieldvalue)

while executing the procedure how to pass the value... or procedure itself has problem...?
Help me...

Tx in Advance...Once again dynamic SQL seems to be the deal here, unless a more complete description of the problem is available.
The thing here is that you cannot do a procedure that works in the way you describe without using dynamic SQL, because of the datatype clash.

Your IN clause wants to do comparisons with integers, and therefore your argument to the procedure will never be able to work unless you convert your values in the fieldvalue variable to a string which you add to the the last part of your existing query, and execute it with dynamic SQL. I cannot judge whether this is the right decision to do in your particular case without more information about the problem. Hope I got your thinking going atleast ...|||In your procedure, parse the parameter into a temp table. Join your temp table to the production table to limit the result set. Works every time, and often runs much faster than the dynamic SQL solution.

-PatP

passing empty string to stored procedure -SQL Express 2005

I am taking data from a form and passing it to a stored procedure to insert
into a table. If there is nothing entered in the field I receive the
following error message:
"Parameter object is improperly defined. Inconsistent or incomplete
information was provided"
The stored procedure is:
ALTER PROCEDURE [dbo].[AddNewContract]
@.strContractorName nVARCHAR(50),
@.strOrderNumber nVARCHAR(50) = null,
@.strWorkLocation ntext = null,
@.Report datetime = null,
@.NewContractID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.tblAcceptContract (ContractorName, OrderNumber,
WorkLocation, SubmitDate,)
SELECT @.strContractorName, @.strOrderNumber, @.strWorkLocation,
@.dtReport;
SELECT @.NewContractID = SCOPE_IDENTITY();
END
The VB code is:
With MyCmd
.ActiveConnection = conn
.CommandText = "dbo.AddNewContract"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@.ContractorName", adVarChar,
adParamInput, Len(strContractorName), strContractorName)
.Parameters.Append .CreateParameter("@.OrderNumber", adVarChar,
adParamInput, Len(strOrderNumber), strOrderNumber)
.Parameters.Append .CreateParameter("@.WorkLocation", adLongVarChar,
adParamInput, Len(strWorkLocation), strWorkLocation)
.Parameters.Append .CreateParameter("@.dtReport", adDate, adParamInput,
Len(dtReport), dtReport)
.Parameters.Append .CreateParameter("@.NewContractID", adInteger,
adParamOutput)
End With
MyCmd.Execute
lContractID = MyCmd.Parameters("@.NewContractID").Value()
I don't know if nothing is passed to the stored procedure if there is no
data in the field or a null is passed.
I though I could just set the default to null in the stored procedure, but
the above error message is displayed. I'd appreciate it if you could let me
know how I pass the empty/null string?Just some ideas, not sure if they will solve the problem or not...
I think that setting your parameter defaults to null is redundant, as the
default only gets used if a null parameter is passed to begin with. To pass
a null rather than an empty string, set the parameter to VBNull.Value (at
least that is what you use in VB.Net). You can pass an empty string for
varchar parameters, but not for date or numberic parameters.
Also, you could run into issues with your NTEXT value if it is very long
(over 4000 characters?).
Before you make any changes, however, step through your VB code and verify
that your variables are populated with the data you expect. If you can't
step through the code, at least print out the values of your variables.
Once you have confirmed what the values are that you are passing, you will
have a better idea of what is going wrong.
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:70464E51-9AB4-4DD8-873A-DC6427188B8E@.microsoft.com...
> I am taking data from a form and passing it to a stored procedure to
insert
> into a table. If there is nothing entered in the field I receive the
> following error message:
> "Parameter object is improperly defined. Inconsistent or incomplete
> information was provided"
> The stored procedure is:
> ALTER PROCEDURE [dbo].[AddNewContract]
> @.strContractorName nVARCHAR(50),
> @.strOrderNumber nVARCHAR(50) = null,
> @.strWorkLocation ntext = null,
> @.Report datetime = null,
> @.NewContractID INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT dbo.tblAcceptContract (ContractorName, OrderNumber,
> WorkLocation, SubmitDate,)
> SELECT @.strContractorName, @.strOrderNumber, @.strWorkLocation,
> @.dtReport;
> SELECT @.NewContractID = SCOPE_IDENTITY();
> END
> The VB code is:
> With MyCmd
> .ActiveConnection = conn
> .CommandText = "dbo.AddNewContract"
> .CommandType = adCmdStoredProc
> .Parameters.Append .CreateParameter("@.ContractorName", adVarChar,
> adParamInput, Len(strContractorName), strContractorName)
> .Parameters.Append .CreateParameter("@.OrderNumber", adVarChar,
> adParamInput, Len(strOrderNumber), strOrderNumber)
> .Parameters.Append .CreateParameter("@.WorkLocation", adLongVarChar,
> adParamInput, Len(strWorkLocation), strWorkLocation)
> .Parameters.Append .CreateParameter("@.dtReport", adDate, adParamInput,
> Len(dtReport), dtReport)
> .Parameters.Append .CreateParameter("@.NewContractID", adInteger,
> adParamOutput)
> End With
> MyCmd.Execute
> lContractID = MyCmd.Parameters("@.NewContractID").Value()
> I don't know if nothing is passed to the stored procedure if there is no
> data in the field or a null is passed.
> I though I could just set the default to null in the stored procedure, but
> the above error message is displayed. I'd appreciate it if you could let
me
> know how I pass the empty/null string?|||> I think that setting your parameter defaults to null is redundant, as the
> default only gets used if a null parameter is passed to begin with.
Not quite true. Defaults get used when parameters are not set at all by the
caller, or if the caller specifies DEFAULT as the parameter value.
Lisa,
Validate user data before calling a procedure, and only append parameters
(to the command object) that have no defaults - i.e. make sure the user
enters all necessary values or create some defaults in the application.
The error message you see is not a SQL Server error.
ML
http://milambda.blogspot.com/|||Jim, ML
Thank you for your feedback, unfortunately I haven't been able to try it out
as I had the insane idea to upgrade my SQL Express to SP1. The upgrade faile
d
and I now cannot uninstall or reinstall, so I'm only left with the option of
setting up another PC. Thankfully I did backup my database before I started
the upgrade!!! (Thanks Microsoft for giving me such challenges.)
I'll let you know how I got on when I'm able to resume.
"ML" wrote:

> Not quite true. Defaults get used when parameters are not set at all by th
e
> caller, or if the caller specifies DEFAULT as the parameter value.
> Lisa,
> Validate user data before calling a procedure, and only append parameters
> (to the command object) that have no defaults - i.e. make sure the user
> enters all necessary values or create some defaults in the application.
> The error message you see is not a SQL Server error.
>
> ML
> --
> http://milambda.blogspot.com/|||"I think that setting your parameter defaults to null is redundant, as
the
default only gets used if a null parameter is passed to begin with. To
pass
a null rather than an empty string, set the parameter to VBNull.Value
(at
least that is what you use in VB.Net). You can pass an empty string
for
varchar parameters, but not for date or numberic parameters. "
I just wanted to quickly mention that the parameter is DBNull.Value,
not VBNull.Value.

Passing documents > 8000 chars to sp_xml_preparedocument

We are storing incoming xmldocumkents in a TEXT column of a table.
I've been trying to find a way of lifting that text data out of the table and passing the data to sp_xml_preparedocument for further processing with OPENXML.
This is fine if the data is < 8000 bytes, however our documents are going to be larger than that. Given that we cannot create a local variable of type TEXT, how do we work around this?
We made one attempt to chunk the data into a temporary table and rebuild it into an sp TEXT parameter as below, but it didn't work.
Any ideas greatly appreciated
Thanks
Mark
CREATE TABLE [dbo].[tblMessage] (
[tblMessageId] [int] IDENTITY (1, 1) NOT NULL ,
[tblmessageXML] [text] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
--This is still < 8000
INSERT INTO tblMessage (tblmessageXML) VALUES ('<NewDataSet><Table><tblPrimaryQueueID>5</tblPrimaryQueueID><tblPeopleStagingID>9</tblPeopleStagingID><tblPeopleStagingStatus>0</tblPeopleStagingStatus><tblPeopleStagingPeopleID>1 6</tblPeopleStagingPeopleID><
tblPeopleStagingPeopleFName>Homer</tblPeopleStagingPeopleFName><tblPeopleStagingPeopl eLName>Simpson</tblPeopleStagingPeopleLName><tblPeopleStagingPeopl eCauswayURN>16201</tblPeopleStagingPeopleCauswayURN></Table></NewDataSet>')
create procedure xmltest2(@.XMLTEXT TEXT)
AS
declare @.dl as int
declare @.Ct as int
declare @.cs as Int
--declare @.XMLtext as VARCHAR(8000)
declare @.tmpXML as VARCHAR(8000)
declare @.MessID as INT
SET @.MessID = 1
set @.cs = 20
select @.dl = DataLength(tblMessageXML) from tblMessage
drop table #t
create table #t (tid INT IDENTITY, tmid INT, tvc VARCHAR(20))
SET @.ct = 1
WHILE @.CT < @.DL
BEGIN
INSERT INTO #t (tmid, tvc)
SELECT @.MessID, SUBSTRING(tblMessageXML, @.CT, @.cs)
FROM tblMessage
--WHERE tblMessageID = @.MessID
SET @.CT = @.CT + @.CS
END
DECLARE XML_CURSOR CURSOR FAST_FORWARD
FOR
SELECT tvc
FROM #t
ORDER BY TID
OPEN XML_CURSOR
FETCH NEXT FROM XML_CURSOR INTO @.TMPXML
--SET @.XMLTEXT = ''
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.XMLTEXT = @.XMLTEXT + @.TMPXML
FETCH NEXT FROM XML_CURSOR INTO @.TMPXML
END
CLOSE XML_CURSOR
DEALLOCATE XML_CURSOR
Hi
Check out:
http://sqlxml.org/faqs.aspx?faq=42
John
"Mark McCormick" <anonymous@.discussions.microsoft.com> wrote in message
news:0C8F6CD3-A116-40C4-9468-9B31F975B32F@.microsoft.com...
> We are storing incoming xmldocumkents in a TEXT column of a table.
> I've been trying to find a way of lifting that text data out of the table
and passing the data to sp_xml_preparedocument for further processing with
OPENXML.
> This is fine if the data is < 8000 bytes, however our documents are going
to be larger than that. Given that we cannot create a local variable of
type TEXT, how do we work around this?
> We made one attempt to chunk the data into a temporary table and rebuild
it into an sp TEXT parameter as below, but it didn't work.
> Any ideas greatly appreciated
> Thanks
> Mark
>
> CREATE TABLE [dbo].[tblMessage] (
> [tblMessageId] [int] IDENTITY (1, 1) NOT NULL ,
> [tblmessageXML] [text] COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> --This is still < 8000
> INSERT INTO tblMessage (tblmessageXML) VALUES
('<NewDataSet><Table><tblPrimaryQueueID>5</tblPrimaryQueueID><tblPeopleStagi
ngID>9</tblPeopleStagingID><tblPeopleStagingStatus>0</tblPeopleStagingStatus
><tblPeopleStagingPeopleID>16</tblPeopleStagingPeopleID><tblPeopleStagingPeo
pleFName>Homer</tblPeopleStagingPeopleFName><tblPeopleStagingPeopl eLName>Sim
pson</tblPeopleStagingPeopleLName><tblPeopleStagingPeopl eCauswayURN>16201</t
blPeopleStagingPeopleCauswayURN></Table></NewDataSet>')
> create procedure xmltest2(@.XMLTEXT TEXT)
> AS
> declare @.dl as int
> declare @.Ct as int
> declare @.cs as Int
> --declare @.XMLtext as VARCHAR(8000)
> declare @.tmpXML as VARCHAR(8000)
> declare @.MessID as INT
> SET @.MessID = 1
> set @.cs = 20
> select @.dl = DataLength(tblMessageXML) from tblMessage
> drop table #t
> create table #t (tid INT IDENTITY, tmid INT, tvc VARCHAR(20))
> SET @.ct = 1
> WHILE @.CT < @.DL
> BEGIN
> INSERT INTO #t (tmid, tvc)
> SELECT @.MessID, SUBSTRING(tblMessageXML, @.CT, @.cs)
> FROM tblMessage
> -- WHERE tblMessageID = @.MessID
> SET @.CT = @.CT + @.CS
> END
> DECLARE XML_CURSOR CURSOR FAST_FORWARD
> FOR
> SELECT tvc
> FROM #t
> ORDER BY TID
>
> OPEN XML_CURSOR
> FETCH NEXT FROM XML_CURSOR INTO @.TMPXML
> --SET @.XMLTEXT = ''
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.XMLTEXT = @.XMLTEXT + @.TMPXML
> FETCH NEXT FROM XML_CURSOR INTO @.TMPXML
> END
> CLOSE XML_CURSOR
> DEALLOCATE XML_CURSOR
>
>
|||Another good one to have a look at
http://www.experts-exchange.com/Data..._20670044.html

Passing data to SQL Server

How to pass big set of data from ASP to SQL Server stored procedure (for example 30 rows and 5 columns - content of html table) ?Stored procedures cannot accept recordsets as parameters, so you will need to pass the data to the procedure one record at a time.

Another alternative is to insert your data into a staging table. Then your application either calls the stored procedure or you create a scheduled job that calls the stored procedure. The procedure checks the staging table for records and processes them as a set.

blindman|||What about XML?sql

Passing Cursor as SP output parameter

Hi,
I have a SP that uses a temporary table and cursor to give all employee IDs
for a passwed managerid at all level of a hierarchy using a structuretable
that has parentId and childId. The SP runs successfully, but when I am
trying to use the output cursor I get the following error:
Calling SQL:
DECLARE @.childCursor cursor
EXEC dco.getPersonIdChild 'EMGR', 'STR', 6852, @.childCursor OUT
FETCH @.childCursor
CLOSE @.childCursor
DEALLOCATE @.ChildCursor
output:
The variable '@.childCursor' does not currently have a cursor allocated to it
.
Server: Msg 16950, Level 16, State 2, Line 4
The variable '@.childCursor' does not currently have a cursor allocated to it
.
Server: Msg 16950, Level 16, State 2, Line 5
The variable '@.childCursor' does not currently have a cursor allocated to it
.
Any help on this problem will be greatly appreciated.
SangDid you define the childCuror variable as an OUT parm in the proc?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sang" <Sang@.discussions.microsoft.com> wrote in message
news:C7D4D59F-3E84-42BC-B132-67DAF449AB6B@.microsoft.com...
> Hi,
> I have a SP that uses a temporary table and cursor to give all employee ID
s
> for a passwed managerid at all level of a hierarchy using a structuretable
> that has parentId and childId. The SP runs successfully, but when I am
> trying to use the output cursor I get the following error:
> Calling SQL:
> DECLARE @.childCursor cursor
> EXEC dco.getPersonIdChild 'EMGR', 'STR', 6852, @.childCursor OUT
> FETCH @.childCursor
> CLOSE @.childCursor
> DEALLOCATE @.ChildCursor
> output:
> The variable '@.childCursor' does not currently have a cursor allocated to
it.
> Server: Msg 16950, Level 16, State 2, Line 4
> The variable '@.childCursor' does not currently have a cursor allocated to
it.
> Server: Msg 16950, Level 16, State 2, Line 5
> The variable '@.childCursor' does not currently have a cursor allocated to
it.
> Any help on this problem will be greatly appreciated.
> Sang
>|||Yes, please see the following:
ALTER PROCEDURE dco.getPersonIdChild (
@.Code CodeExtraLong,
@.CodeTypeCode CHAR(3),
@.PersonIdParent UniqueId,
@.childCursor CURSOR VARYING OUT
)
AS
"Tibor Karaszi" wrote:

> Did you define the childCuror variable as an OUT parm in the proc?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sang" <Sang@.discussions.microsoft.com> wrote in message
> news:C7D4D59F-3E84-42BC-B132-67DAF449AB6B@.microsoft.com...
>|||I think you need to give us more to go on. Below work fine for me:
USE pubs
GO
CREATE PROC p @.c cursor varying OUT
AS
SET @.c = CURSOR LOCAL FOR SELECT au_lname FROM authors
OPEN @.c
GO
DECLARE @.oc cursor
EXEC p @.c = @.oc OUTPUT
FETCH NEXT FROM @.oc
GO
DROP PROC p
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sang" <Sang@.discussions.microsoft.com> wrote in message
news:1E16242A-4AD9-4F61-8781-13F833E85369@.microsoft.com...
> Yes, please see the following:
> ALTER PROCEDURE dco.getPersonIdChild (
> @.Code CodeExtraLong,
> @.CodeTypeCode CHAR(3),
> @.PersonIdParent UniqueId,
> @.childCursor CURSOR VARYING OUT
> )
> AS
> "Tibor Karaszi" wrote:
>|||The SP is the following:
ALTER PROCEDURE dco.getPersonIdChild (
@.Code CodeExtraLong,
@.CodeTypeCode CHAR(3),
@.PersonIdParent UniqueId,
@.childCursor CURSOR VARYING OUT
)
AS
BEGIN
DECLARE
@.Level smallint,
@.PersonIdParentC UniqueID
-- Let the intial set of children for the passed parentId
SET @.Level = 1
IF OBJECT_ID ('#tempTable') IS NOT NULL
Drop Table #tempTable
CREATE TABLE #TempTable (PersonIdChild UniqueId, Depth smallint, Checked
bit)
INSERT INTO #TempTable
SELECT PersonIdChild, @.Level AS "Depth", 0 AS "Checked"
FROM PersonStructures
WHERE Code = @.Code
AND CodeTypeCode = @.CodeTypeCode
AND PersonIdParent = @.PersonIdParent
AND EndDate is NULL
WHILE ((SELECT count(*) from #TempTable WHERE Checked = 0 ) > 0)
BEGIN
DECLARE Children CURSOR
FOR
SELECT PersonIdChild
FROM #TempTable
WHERE Depth = @.Level
OPEN Children
FETCH Children into @.PersonIdParentC
WHILE (@.@.FETCH_STATUS =0)
BEGIN
-- PRINT '@.Level is ' + CAST(@.Level AS CHAR)
IF ((SELECT Count(*) from PersonStructures
WHERE Code = @.Code
AND CodeTypeCode = @.CodeTypeCode
AND PersonIdParent = @.PersonIdParentC
AND EndDate is NULL) > 0 )
BEGIN
INSERT INTO #TempTable
SELECT PersonIdChild, @.Level + 1, 0
FROM PersonStructures
WHERE Code = @.Code
AND CodeTypeCode = @.CodeTypeCode
AND PersonIdParent = @.PersonIdParentC
AND EndDate is NULL
END
-- Now Update the checked column for the record just processed
UPDATE #TempTable
SET Checked = 1
WHERE PersonIdChild = @.PersonIdParentC
FETCH NEXT FROM Children INTO @.PersonIdParentC
END
CLOSE Children
DEALLOCATE Children
SET @.Level = @.Level + 1
END
DECLARE s CURSOR
LOCAL
FOR SELECT PersonIdChild, Depth
FROM #TempTable
SET @.childCursor = s
OPEN @.childCursor
RETURN (0)
END
"Tibor Karaszi" wrote:

> I think you need to give us more to go on. Below work fine for me:
> USE pubs
> GO
> CREATE PROC p @.c cursor varying OUT
> AS
> SET @.c = CURSOR LOCAL FOR SELECT au_lname FROM authors
> OPEN @.c
> GO
> DECLARE @.oc cursor
> EXEC p @.c = @.oc OUTPUT
> FETCH NEXT FROM @.oc
> GO
> DROP PROC p
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sang" <Sang@.discussions.microsoft.com> wrote in message
> news:1E16242A-4AD9-4F61-8781-13F833E85369@.microsoft.com...
>sql

Passing Column Name as parameter to sql store procedure

i am using asp.net 2005 with sql server 2005. in my database table contains

Table Name : Page_Content

Page_Id

1011021AbcPqr2Lmnoiu

ALTER PROCEDURE[dbo].[SELECT_CONTENT]

(@.lang_codevarchar(max))

AS

begin

declare@.aas varchar(max)set@.a = @.lang_code

Selectpage_id,@.aFrompage_content

end

Here in this above store procedure i want to pass 101 to @.lang_code

here is my output, but this is wrong output

Page_Id

Column111012101

but i want following output

Page_Id

1011021AbcPqr2Lmnoiu

use dynamic sql.http://www.sommarskog.se/dyn-search.html
modify your procedure as:-

declare @.sql
set @.sql = 'Selectpage_id,' + @.a + 'Frompage_content'
exec sp_executesql @.sql

hope it helps

|||

aadreja:

use dynamic sql.http://www.sommarskog.se/dyn-search.html
modify your procedure as:-

declare @.sql
set @.sql = 'Selectpage_id,' + @.a + 'Frompage_content'
exec sp_executesql @.sql

hope it helps

The above code is subject to sql injection attacks. Query on sql injection attacks if you don't know what they are.

As coded, someone could force your page to reveal sensitive data in other tables, or alter or destroy data in your database in ways you do not want to allow.

Given that a column name has very specific naming rules, you can test that the value you get in @.a is a plausible, safe column name.

If @.a has any character other than a letter from a-z, A-Z or 0-9, you should trap that and raise an error.

One way to test is to make a copy of @.a and remove all the valid characters. If nothing is left in the copy, it's a safe column name to process.

|||

I know you could do this with dynamic sql, but that's not always the best solution.

I think you could use a CASE/WHEN block to do what you are wanting. Each field would have to be known in advance, this wouldn't work "on the fly" if you add new columns to the table without updating the SP

ALTER PROCEDURE[dbo].[SELECT_CONTENT]

(@.lang_codevarchar(max))

AS

begin

declare@.aas varchar(max)set@.a = @.lang_code

Selectpage_id,
CASE
WHEN @.a = '101' THEN 101
WHEN @.a = '102' THEN 102
ELSE 101 -- you don't need an else, but this query will fail in a syntax error if the input doesn't match one of your defined values.
END Frompage_content

end

|||

I agree!

Benefits of your approach:

Sql Injection safe

passing column name as parameter to a stored procedure

Hi!
I want to pass a column name, sometimes a table name, to a stored
procedure, but it didn't work. I tried to define the data type as
char, vachar, nchar, text, but the result were same. Any one know how
to get it work?
Thanks a lot!!
Saiyou[posted and mailed, please reply in news]

Saiyou Anh (wangc@.alexian.net) writes:
> I want to pass a column name, sometimes a table name, to a stored
> procedure, but it didn't work. I tried to define the data type as
> char, vachar, nchar, text, but the result were same. Any one know how
> to get it work?

So why do you need to do this?

While this is possible to do this, you might essentially be throwing
out the baby with the bathtub and loose most of the advantages of
stored procedures. Often this is a token of bad design.

Anyway, I have a longer article on my web site that shows you how to do
it - and why you probably shouldn't.

http://www.sommarskog.se/dynamic_sql.html

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Passing Column Name as Parameter (SPROC)

Hello, I don't know a lot of T-SQL. I have a table with 49 columns and 48 rows (US States). The fist column is a list of "from" states and the other columns are named for each state. The point is to look up a variable column for a variable row to obtain a state to state transit rate. I tried everything I could find and no luck. If someone can help I would appreciate it, and if not thanks anyway. Gregg

You could use dynamic SQL from this problem:

Code Snippet

create table rates

(

from_state char(2),

WA decimal,

NY decimal,

CA decimal

)

insert into rates values('WA',0,1,10)

insert into rates values('NY',2,0,20)

insert into rates values('CA',3,30,0)

create procedure GetRate

@.from_state char(2),

@.to_state char(2)

AS

BEGIN

declare @.query varchar(100)

set @.query = 'select '+@.to_state+' from rates where from_state='''+@.from_state+''''

execute ( @.query)

END

But this method has SQL Injections.

Partly you could solve this problem by following code:

Code Snippet

create procedure GetRate2

@.from_state char(2),

@.to_state char(2)

AS

BEGIN

declare @.query nvarchar(100)

set @.query = 'select '+@.to_state+' from rates where from_state=@.from_state'

EXEC sp_executesql @.query, N'@.from_state char(2)', @.from_state=@.from_state

END

But best solution is convert you table for following format:

Code Snippet

create table rates2

(

from_state char(2),

to_state char(2),

rate decimal

)

|||

Here the sample ..

Code Snippet

--Sample table (first 10 States)

Create Table #statesfare (

[Starting From] Varchar(100) ,

[Alabama] Varchar(100) ,

[Alaska] Varchar(100) ,

[Arizona] Varchar(100) ,

[Arkansas] Varchar(100) ,

[California] Varchar(100) ,

[Colorado] Varchar(100) ,

[Connecticut] Varchar(100) ,

[Delaware] Varchar(100) ,

[Florida] Varchar(100) ,

[Georgia] Varchar(100)

);

--Random Generated data (Not For Scale, But City From & To same then ZERO)

Insert Into #statesfare Values('Alabama','0','21','29','90','82','65','72','84','4','51');

Insert Into #statesfare Values('Alaska','33','0','17','80','37','92','90','21','12','20');

Insert Into #statesfare Values('Arizona','62','19','0','97','23','66','22','43','94','60');

Insert Into #statesfare Values('Arkansas','61','38','12','0','98','42','68','70','81','87');

Insert Into #statesfare Values('California','92','27','82','72','0','43','84','39','24','80');

Insert Into #statesfare Values('Colorado','72','34','97','52','52','0','10','38','64','40');

Insert Into #statesfare Values('Connecticut','100','78','27','18','74','3','0','67','26','48');

Insert Into #statesfare Values('Delaware','93','49','20','88','45','3','60','0','57','77');

Insert Into #statesfare Values('Florida','79','80','37','12','90','30','24','48','0','50');

Insert Into #statesfare Values('Georgia','33','46','16','30','46','72','42','85','18','0');

Declare @.StartFrom as varchar(100);

Declare @.EndAt as varchar(100);

--Sample Input

Set @.StartFrom = 'Alabama'

Set @.EndAt = 'Georgia'

--Using Dynamic SQL (Supports Both SQL Server 2000 & 2005)

Exec ('Select ' + @.EndAt + ' Fare From #statesfare Where [Starting From] =''' +@.StartFrom + '''');

--Using UNPIVOT operator Only on SQL Server 2005

Select

[Starting From]

,[Target States]

,Fares

From

#statesfare P

UNPIVOT

(

Fares FOR [Target States] IN

(

[Alabama],

[Alaska],

[Arizona],

[Arkansas],

[California],

[Colorado],

[Connecticut],

[Delaware],

[Florida],

[Georgia]

)

) as UPVT

Where

[Starting From] = @.StartFrom

And [Target States] = @.EndAt

|||

I recommend to use quotename() function to avoid sql injection.

Code Snippet

set @.qeury = 'select ' + quotename(@.to_sate) + ' from ....';

quotename('abc') returns '[abc]';

Regards,

|||This is my table

FROM AL AR AZ CA AL 1.75 1.95 1.10 1.75 AR 1.50 1.50 1.50 1.75 AZ 1.50 1.50 1.50 1.75 CA 1.50 1.50 1.50 1.75

I Need to use a stored procedure to select one of these decimal rates based on two input parameters (@.fromstate AND @.tostate) and return the rate as an out put parameter(@.rate). I tried this even though I knew it was too simple to worrk.

ALTER PROCEDURE RC_Get_Rate

(

@.origstate nvarchar(255),

@.deststate nvarchar(255),

@.rate nvarchar(255) OUTPUT

)

AS

SET NOCOUNT ON

BEGIN

SELECT @.rate = @.deststate

FROM Rates48

WHERE Origin_State = @.origstate

END

RETURN

I'm sorry if you're previous posts already answered this, I thought I might not have been clear enougn as to my situation and my goal. I really did search a lot on this topic but got nowhere. Again if you can help (or already did) thanks, and if you can't thanks anyway, Gregg|||

here you go...

Code Snippet

Create Table rates48 (

[Origin_State] Varchar(100) ,

[AL] float ,

[AR] float ,

[AZ] float ,

[CA] float

);

Go

Insert Into rates48 Values('AL','1.75','1.95','1.10','1.75');

Insert Into rates48 Values('AR','1.50','1.50','1.50','1.75');

Insert Into rates48 Values('AZ','1.50','1.50','1.50','1.75');

Insert Into rates48 Values('CA','1.50','1.50','1.50','1.75');

Go

Create PROCEDURE RC_Get_Rate

(

@.origstate nvarchar(255),

@.deststate nvarchar(255),

@.rate nvarchar(255) OUTPUT

)

AS

SET NOCOUNT ON

BEGIN

Declare @.Query as NVarchar(1000);

Set @.Query = N'SELECT @.rateout = ' + quotename(@.deststate) + '

FROM Rates48

WHERE Origin_State = @.origstatein';

Exec sp_executesql @.Query, N'@.origstatein varchar(255), @.rateout nvarchar(255) output',@.origstatein = @.origstate, @.rateout=@.rate OUTPUT;

Return;

END

Go

Declare @.rate nvarchar(255)

Exec RC_Get_Rate 'AL', 'AZ', @.rate OUTPUT

Select @.rate

|||Thank You very much Manivannan.D.Sekaran. That works very well. I must admit though I don't know how. Could you maybe recommend a good book I could pick up to further myself on T-SQL. I would like to learn SPROC's, Functions, and some more advanced SQL. Well, again, thank you very much Manivannan.D.Sekaran, and also thank you to everyone who took the time to read my post and help me out. I really appreciate it. Thanks, Gregg

Passing back NULL

Is there any way in a query to pass back a NULL if no data is found?
I have this query that is looking at our Customer table and some customers
may not exist but in that case I want to pass back a NULL...
My SQL looks like so...
SELECT *
FROM _CUSTOMER_
WHERE (NAME LIKE 'Alway%' AND FNAME ='Susanna')
OR (NAME LIKE 'Abaquin%' AND FNAME ='Paul')
OR (NAME LIKE 'Abbott%' AND FNAME ='Cindy')
OR (NAME LIKE 'Abney%' AND FNAME ='Linda')
OR (NAME LIKE 'Abraham%' AND FNAME ='Jo')
OR (NAME LIKE 'Abrams %' AND FNAME ='Jeff')
The first name, Susanna Alway does NOT exist...so in that case I'd like to
pass pack a NULL.
These groups are the best!
Please help me out!
Thanks!A null.....? A single null column? An empty resultset?
Thomas
"RTP" <RTP@.discussions.microsoft.com> wrote in message
news:8847FFB7-02DA-4357-A62C-A95BE3B18576@.microsoft.com...
> Is there any way in a query to pass back a NULL if no data is found?
> I have this query that is looking at our Customer table and some customers
> may not exist but in that case I want to pass back a NULL...
> My SQL looks like so...
> SELECT *
> FROM _CUSTOMER_
> WHERE (NAME LIKE 'Alway%' AND FNAME ='Susanna')
> OR (NAME LIKE 'Abaquin%' AND FNAME ='Paul')
> OR (NAME LIKE 'Abbott%' AND FNAME ='Cindy')
> OR (NAME LIKE 'Abney%' AND FNAME ='Linda')
> OR (NAME LIKE 'Abraham%' AND FNAME ='Jo')
> OR (NAME LIKE 'Abrams %' AND FNAME ='Jeff')
> The first name, Susanna Alway does NOT exist...so in that case I'd like to
> pass pack a NULL.
> These groups are the best!
> Please help me out!
> Thanks!|||Yes use If Exists
If Exists (Select * From ...
Where ... ) -- make sure From & Where clause are same as main
query
Select <Stuff> From ...
Where ...
Order By ...
Else
Select Null As Col1Name, Null as Col2Name,
Null as Col3Name, etc...
After your query
"RTP" wrote:

> Is there any way in a query to pass back a NULL if no data is found?
> I have this query that is looking at our Customer table and some customers
> may not exist but in that case I want to pass back a NULL...
> My SQL looks like so...
> SELECT *
> FROM _CUSTOMER_
> WHERE (NAME LIKE 'Alway%' AND FNAME ='Susanna')
> OR (NAME LIKE 'Abaquin%' AND FNAME ='Paul')
> OR (NAME LIKE 'Abbott%' AND FNAME ='Cindy')
> OR (NAME LIKE 'Abney%' AND FNAME ='Linda')
> OR (NAME LIKE 'Abraham%' AND FNAME ='Jo')
> OR (NAME LIKE 'Abrams %' AND FNAME ='Jeff')
> The first name, Susanna Alway does NOT exist...so in that case I'd like to
> pass pack a NULL.
> These groups are the best!
> Please help me out!
> Thanks!|||That won't work because the EXISTS will return a TRUE condition for the OR i
n
the query. I have to build this for 7,000 names so I can't query each name
individually. I'm thinking of using a JOIN which will pass back NULLS if the
row doesn't exist. My problem here is that a user messed up a whole
spreadsheet where there are e-mail addresses and I'm cutting and pasting
these 7,000 names out of an e-mail spreadsheet and then using Query Analyzer
to go up against the database to see if we have their e-mail address. The
7,000 names include those people which may NOT be in out _CUSTOMER_ table an
d
there is no way of determining whether they're in the database or not until
I
query.
Any help or insight would be GREATLY appreciated!!!
"CBretana" wrote:
> Yes use If Exists
> If Exists (Select * From ...
> Where ... ) -- make sure From & Where clause are same as main
> query
> Select <Stuff> From ...
> Where ...
> Order By ...
> Else
> Select Null As Col1Name, Null as Col2Name,
> Null as Col3Name, etc...
>
> After your query
> "RTP" wrote:
>|||Since this sounds like a one-time thing, then write a script that cycles thr
ough
each address from the spreadshet and does whatever sophisticated checking an
d
data cleasing is necesassry.
If you do that in T-SQL, then you'll use cursors or you could do it in some
other language like VBA from Excel.
Thomas
"RTP" <RTP@.discussions.microsoft.com> wrote in message
news:098909F0-4473-4145-94EC-056005EB917A@.microsoft.com...
> That won't work because the EXISTS will return a TRUE condition for the OR
in
> the query. I have to build this for 7,000 names so I can't query each name
> individually. I'm thinking of using a JOIN which will pass back NULLS if t
he
> row doesn't exist. My problem here is that a user messed up a whole
> spreadsheet where there are e-mail addresses and I'm cutting and pasting
> these 7,000 names out of an e-mail spreadsheet and then using Query Analyz
er
> to go up against the database to see if we have their e-mail address. The
> 7,000 names include those people which may NOT be in out _CUSTOMER_ table
and
> there is no way of determining whether they're in the database or not unti
l I
> query.
> Any help or insight would be GREATLY appreciated!!!
> "CBretana" wrote:
>

Passing Array with ids to stored procedure

I want to pass and array of ids to a procedure for inserting i a relation table.

I found some examples in other posts, but had problems getting them to work.

I just want to pass a parameter with value like '1,45,89' to the procedure, then loop through it to insert the relations.

(I´m using sql server 2000), had some problem with examples with strpos then.

Any hints ?

peace.

Create procedure ParseArray
( @.Array varchar(1000),
@.separator char(1) )
AS
-- Created by graz@.sqlteam.com
set nocount on
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
declare @.separator_position int -- This is used to locate each separator character
declare @.array_value varchar(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
while patindex('%' + @.separator + '%' , @.array) <> 0
begin
 -- patindex matches the a pattern against a string
 select @.separator_position = patindex('%' + @.separator + '%' , @.array)
 select @.array_value = left(@.array, @.separator_position - 1)
 -- This is where you process the values passed.
 -- Replace this select statement with your processing
 -- @.array_value holds the value of this element of the array
 select Array_Value = @.array_value
 -- This replaces what we just processed with and empty string
 select @.array = stuff(@.array, 1, @.separator_position, '')
end
set nocount off
go

enough documentation to xplain whats going on...

hth|||Don't bother using arrays (well until Yukon) this is one area where passing XML is helpful. Construct a simple XML string <r><i x="10" /><i x="10"/></r> etc and pass that as text to the proc (if you're client object supports serialization the xml might already be there). In the proc convert the XML into a table var. Then off you go, the array is now a cell per row.

Passing array to stored procedure

Can some one tell me the way to pass Table or Array or list of values to stored procedure?

[Note: I already know some ways which are not useful in my case:

- By passing a list of values separting each with a delimeter like comma(,)

- Using XML

]

Any other way?

Please, its urgent

Thanks in advance

http://www.google.de/search?hl=de&q=array+stored+procedure&meta=

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens...

The reference sites you provided mentioned things I already tried...

Please can you show me other ways to do it...

Cause my project has bulky transactions.... So these methods won't work here....

I need to pass Entire Table or Array from C# (Front end) to stored procedure...

and also multiple user (more than 100 concurrent users) access same resources at the same time...So I cannot use XML (file systems) for this task.

I need to pass these data in other ways...

Please help me out...

|||

How about creating a table in the database which has two fields (userId, value), assuming that the lowest granularity of the concurrence is at user level, and filling it with all the data you want to pass and call the stored procedure with userId as a parameter.

The stored procedure reads the values from the table where userId = @.userId and deletes the values at the end to keep the table clean.

|||A simple row can be passed using a comma delimited string and splitting this on the server to the appriopate values. For more than one row you would have to use a more sophisticated funtionality. The lat option would be to use something like a materialized table, as the previous poster already mentioned.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks Jens and ,

I tried as you said, I created a class in C# which creates a global temporary table in SQL Server, then I executed Stored procedure which manipulates that temporary table, and finally when stored procedure does its work, this class deletes the temporary table.

To avoid conflicts in session and user from using same table, I suffixed the table with date time when the table was created....

This table is dynamic, it can be created by passing DataTable from C#....

It solved my problem....

Thanks both of you for giving me idea....

|||

Hi,

Jens and Lakshmana

The Idea you guys gave me helped me a lot.

I created a class (in C#) that first creates a global temporary table in SQL Server, then executes stored procedure. This procedure

manipulates the temporary table and takes data stored in this table. When stored procedure finishes its task, the class in C#, deletes the temporary table.

To avoid conflict in sessions and multiple users from using same table, I suffixed the table with date time when the table is being created...

This helped me a lot (though it is a negative from performance point of view)...

Thank you guys....

|||Hi All|||

Hi All

thnx for ur comments

really its all Usefull

i Just Have small Problem .

after i Created the global temporary table in SQL Server , and Inserted the Data i wanna use in the Stored Procedure

i Pass the temporary table Name to the Stored Procedure

But i couldnt use it with aCursor or even Normal Select Statement.

coz i have to concatinate it in the select Statemant

Searchin For Help

Thanks for all of u

|||Hi All.
This is one problem that kept on following me. I tried all the other suggestions and it worked fine at the time. Recently I decided to look for an alternative that will give me more flexibility.
My problem was I need to pass a list of keys to a stored proc (sp). The system I work on was architectured to work over slow networks and the internet, so no unnecessary call to the backend, hence my need to pass a list of keys.

I pass my keys in as a delimeted string (delimeter can be "," or "|" or any chosen character, this will be more clear later).
On the database I have a table-value function that convert this delimeted string to a table and this allows me to use it in joins and "where fk_somekey in (select * strval from delimtable)" statements.
Here is the function:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Lucian@.Probia.co.za
-- Create date: 2006/11/22
-- Description: Returns table for delimited string
-- =============================================
Create FUNCTION [dbo].[DelimStrToTable]
(
-- Add the parameters for the function here
@.delimStr varchar(8000),
@.delimchar char
)
RETURNS
@.StrValTable TABLE
(
-- Add the column definitions for the TABLE variable here
StrVal varchar(1000)

)
AS
BEGIN
-- Fill the table variable with the rows for your result set
declare @.strlist varchar(8000), @.pos int, @.delim char, @.lstr varchar(1000)
set @.strlist = @.delimStr
set @.delim = @.delimchar

while ((len(@.strlist) > 0) and (@.strlist <> ''))
begin
set @.pos = charindex(@.delim, @.strlist)

if @.pos > 0
begin
set @.lstr = substring(@.strlist, 1, @.pos-1)
set @.strlist = ltrim(substring(@.strlist,charindex(@.delim, @.strlist)+1, 8000))
end
else
begin
set @.lstr = @.strlist
set @.strlist = ''
end
Insert @.StrValTable values (@.lstr)
--print @.lstr
end

RETURN
END

and now I can do this:

Select * from Authors where AuthorId in (select convert(int,strval) from DelimStrToTable('1|2|11|45', '|'))

or
Select A.* from Authors A inner join DelimStrToTable('1|2|11|45', '|') as IDLst
on A.AuthorId = convert(int,IDLst.strval)

One is of course limited by the size of stored proc parameter. For most of my scenarios, this worked fine. Hope it serves as an alternative.

Lucian|||I think the line
StrVal varchar(1000)
can be changed to
StrVal varchar(10)
The size here depend on the size of your list item in the parameter. Size of 10 is fine for a list of integers.
sql

Passing array to stored procedure

Can some one tell me the way to pass Table or Array or list of values to stored procedure?

[Note: I already know some ways which are not useful in my case:

- By passing a list of values separting each with a delimeter like comma(,)

- Using XML

]

Any other way?

Please, its urgent

Thanks in advance

http://www.google.de/search?hl=de&q=array+stored+procedure&meta=

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens...

The reference sites you provided mentioned things I already tried...

Please can you show me other ways to do it...

Cause my project has bulky transactions.... So these methods won't work here....

I need to pass Entire Table or Array from C# (Front end) to stored procedure...

and also multiple user (more than 100 concurrent users) access same resources at the same time...So I cannot use XML (file systems) for this task.

I need to pass these data in other ways...

Please help me out...

|||

How about creating a table in the database which has two fields (userId, value), assuming that the lowest granularity of the concurrence is at user level, and filling it with all the data you want to pass and call the stored procedure with userId as a parameter.

The stored procedure reads the values from the table where userId = @.userId and deletes the values at the end to keep the table clean.

|||A simple row can be passed using a comma delimited string and splitting this on the server to the appriopate values. For more than one row you would have to use a more sophisticated funtionality. The lat option would be to use something like a materialized table, as the previous poster already mentioned.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks Jens and ,

I tried as you said, I created a class in C# which creates a global temporary table in SQL Server, then I executed Stored procedure which manipulates that temporary table, and finally when stored procedure does its work, this class deletes the temporary table.

To avoid conflicts in session and user from using same table, I suffixed the table with date time when the table was created....

This table is dynamic, it can be created by passing DataTable from C#....

It solved my problem....

Thanks both of you for giving me idea....

|||

Hi,

Jens and Lakshmana

The Idea you guys gave me helped me a lot.

I created a class (in C#) that first creates a global temporary table in SQL Server, then executes stored procedure. This procedure

manipulates the temporary table and takes data stored in this table. When stored procedure finishes its task, the class in C#, deletes the temporary table.

To avoid conflict in sessions and multiple users from using same table, I suffixed the table with date time when the table is being created...

This helped me a lot (though it is a negative from performance point of view)...

Thank you guys....

|||Hi All|||

Hi All

thnx for ur comments

really its all Usefull

i Just Have small Problem .

after i Created the global temporary table in SQL Server , and Inserted the Data i wanna use in the Stored Procedure

i Pass the temporary table Name to the Stored Procedure

But i couldnt use it with aCursor or even Normal Select Statement.

coz i have to concatinate it in the select Statemant

Searchin For Help

Thanks for all of u

|||Hi All.
This is one problem that kept on following me. I tried all the other suggestions and it worked fine at the time. Recently I decided to look for an alternative that will give me more flexibility.
My problem was I need to pass a list of keys to a stored proc (sp). The system I work on was architectured to work over slow networks and the internet, so no unnecessary call to the backend, hence my need to pass a list of keys.

I pass my keys in as a delimeted string (delimeter can be "," or "|" or any chosen character, this will be more clear later).
On the database I have a table-value function that convert this delimeted string to a table and this allows me to use it in joins and "where fk_somekey in (select * strval from delimtable)" statements.
Here is the function:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Lucian@.Probia.co.za
-- Create date: 2006/11/22
-- Description: Returns table for delimited string
-- =============================================
Create FUNCTION [dbo].[DelimStrToTable]
(
-- Add the parameters for the function here
@.delimStr varchar(8000),
@.delimchar char
)
RETURNS
@.StrValTable TABLE
(
-- Add the column definitions for the TABLE variable here
StrVal varchar(1000)

)
AS
BEGIN
-- Fill the table variable with the rows for your result set
declare @.strlist varchar(8000), @.pos int, @.delim char, @.lstr varchar(1000)
set @.strlist = @.delimStr
set @.delim = @.delimchar

while ((len(@.strlist) > 0) and (@.strlist <> ''))
begin
set @.pos = charindex(@.delim, @.strlist)

if @.pos > 0
begin
set @.lstr = substring(@.strlist, 1, @.pos-1)
set @.strlist = ltrim(substring(@.strlist,charindex(@.delim, @.strlist)+1, 8000))
end
else
begin
set @.lstr = @.strlist
set @.strlist = ''
end
Insert @.StrValTable values (@.lstr)
--print @.lstr
end

RETURN
END

and now I can do this:

Select * from Authors where AuthorId in (select convert(int,strval) from DelimStrToTable('1|2|11|45', '|'))

or
Select A.* from Authors A inner join DelimStrToTable('1|2|11|45', '|') as IDLst
on A.AuthorId = convert(int,IDLst.strval)

One is of course limited by the size of stored proc parameter. For most of my scenarios, this worked fine. Hope it serves as an alternative.

Lucian|||I think the line
StrVal varchar(1000)
can be changed to
StrVal varchar(10)
The size here depend on the size of your list item in the parameter. Size of 10 is fine for a list of integers.

Passing array to stored procedure

Can some one tell me the way to pass Table or Array or list of values to stored procedure?

[Note: I already know some ways which are not useful in my case:

- By passing a list of values separting each with a delimeter like comma(,)

- Using XML

]

Any other way?

Please, its urgent

Thanks in advance

http://www.google.de/search?hl=de&q=array+stored+procedure&meta=

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens...

The reference sites you provided mentioned things I already tried...

Please can you show me other ways to do it...

Cause my project has bulky transactions.... So these methods won't work here....

I need to pass Entire Table or Array from C# (Front end) to stored procedure...

and also multiple user (more than 100 concurrent users) access same resources at the same time...So I cannot use XML (file systems) for this task.

I need to pass these data in other ways...

Please help me out...

|||

How about creating a table in the database which has two fields (userId, value), assuming that the lowest granularity of the concurrence is at user level, and filling it with all the data you want to pass and call the stored procedure with userId as a parameter.

The stored procedure reads the values from the table where userId = @.userId and deletes the values at the end to keep the table clean.

|||A simple row can be passed using a comma delimited string and splitting this on the server to the appriopate values. For more than one row you would have to use a more sophisticated funtionality. The lat option would be to use something like a materialized table, as the previous poster already mentioned.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks Jens and ,

I tried as you said, I created a class in C# which creates a global temporary table in SQL Server, then I executed Stored procedure which manipulates that temporary table, and finally when stored procedure does its work, this class deletes the temporary table.

To avoid conflicts in session and user from using same table, I suffixed the table with date time when the table was created....

This table is dynamic, it can be created by passing DataTable from C#....

It solved my problem....

Thanks both of you for giving me idea....

|||

Hi,

Jens and Lakshmana

The Idea you guys gave me helped me a lot.

I created a class (in C#) that first creates a global temporary table in SQL Server, then executes stored procedure. This procedure

manipulates the temporary table and takes data stored in this table. When stored procedure finishes its task, the class in C#, deletes the temporary table.

To avoid conflict in sessions and multiple users from using same table, I suffixed the table with date time when the table is being created...

This helped me a lot (though it is a negative from performance point of view)...

Thank you guys....

|||Hi All|||

Hi All

thnx for ur comments

really its all Usefull

i Just Have small Problem .

after i Created the global temporary table in SQL Server , and Inserted the Data i wanna use in the Stored Procedure

i Pass the temporary table Name to the Stored Procedure

But i couldnt use it with aCursor or even Normal Select Statement.

coz i have to concatinate it in the select Statemant

Searchin For Help

Thanks for all of u

|||Hi All.
This is one problem that kept on following me. I tried all the other suggestions and it worked fine at the time. Recently I decided to look for an alternative that will give me more flexibility.
My problem was I need to pass a list of keys to a stored proc (sp). The system I work on was architectured to work over slow networks and the internet, so no unnecessary call to the backend, hence my need to pass a list of keys.

I pass my keys in as a delimeted string (delimeter can be "," or "|" or any chosen character, this will be more clear later).
On the database I have a table-value function that convert this delimeted string to a table and this allows me to use it in joins and "where fk_somekey in (select * strval from delimtable)" statements.
Here is the function:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Lucian@.Probia.co.za
-- Create date: 2006/11/22
-- Description: Returns table for delimited string
-- =============================================
Create FUNCTION [dbo].[DelimStrToTable]
(
-- Add the parameters for the function here
@.delimStr varchar(8000),
@.delimchar char
)
RETURNS
@.StrValTable TABLE
(
-- Add the column definitions for the TABLE variable here
StrVal varchar(1000)

)
AS
BEGIN
-- Fill the table variable with the rows for your result set
declare @.strlist varchar(8000), @.pos int, @.delim char, @.lstr varchar(1000)
set @.strlist = @.delimStr
set @.delim = @.delimchar

while ((len(@.strlist) > 0) and (@.strlist <> ''))
begin
set @.pos = charindex(@.delim, @.strlist)

if @.pos > 0
begin
set @.lstr = substring(@.strlist, 1, @.pos-1)
set @.strlist = ltrim(substring(@.strlist,charindex(@.delim, @.strlist)+1, 8000))
end
else
begin
set @.lstr = @.strlist
set @.strlist = ''
end
Insert @.StrValTable values (@.lstr)
--print @.lstr
end

RETURN
END

and now I can do this:

Select * from Authors where AuthorId in (select convert(int,strval) from DelimStrToTable('1|2|11|45', '|'))

or
Select A.* from Authors A inner join DelimStrToTable('1|2|11|45', '|') as IDLst
on A.AuthorId = convert(int,IDLst.strval)

One is of course limited by the size of stored proc parameter. For most of my scenarios, this worked fine. Hope it serves as an alternative.

Lucian|||I think the line
StrVal varchar(1000)
can be changed to
StrVal varchar(10)
The size here depend on the size of your list item in the parameter. Size of 10 is fine for a list of integers.

Passing array to stored procedure

Can some one tell me the way to pass Table or Array or list of values to stored procedure?

[Note: I already know some ways which are not useful in my case:

- By passing a list of values separting each with a delimeter like comma(,)

- Using XML

]

Any other way?

Please, its urgent

Thanks in advance

http://www.google.de/search?hl=de&q=array+stored+procedure&meta=

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens...

The reference sites you provided mentioned things I already tried...

Please can you show me other ways to do it...

Cause my project has bulky transactions.... So these methods won't work here....

I need to pass Entire Table or Array from C# (Front end) to stored procedure...

and also multiple user (more than 100 concurrent users) access same resources at the same time...So I cannot use XML (file systems) for this task.

I need to pass these data in other ways...

Please help me out...

|||

How about creating a table in the database which has two fields (userId, value), assuming that the lowest granularity of the concurrence is at user level, and filling it with all the data you want to pass and call the stored procedure with userId as a parameter.

The stored procedure reads the values from the table where userId = @.userId and deletes the values at the end to keep the table clean.

|||A simple row can be passed using a comma delimited string and splitting this on the server to the appriopate values. For more than one row you would have to use a more sophisticated funtionality. The lat option would be to use something like a materialized table, as the previous poster already mentioned.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks Jens and ,

I tried as you said, I created a class in C# which creates a global temporary table in SQL Server, then I executed Stored procedure which manipulates that temporary table, and finally when stored procedure does its work, this class deletes the temporary table.

To avoid conflicts in session and user from using same table, I suffixed the table with date time when the table was created....

This table is dynamic, it can be created by passing DataTable from C#....

It solved my problem....

Thanks both of you for giving me idea....

|||

Hi,

Jens andLakshmana

The Idea you guys gave me helped me a lot.

I created a class (in C#) that first creates a global temporary table in SQL Server, then executes stored procedure. This procedure

manipulates the temporary table and takes data stored in this table. When stored procedure finishes its task, the class in C#, deletes the temporary table.

To avoid conflict in sessions and multiple users from using same table, I suffixed the table with date time when the table is being created...

This helped me a lot (though it is a negative from performance point of view)...

Thank you guys....

|||Hi All|||

Hi All

thnx for ur comments

really its all Usefull

i Just Have small Problem .

after i Created the global temporary table in SQL Server , and Inserted the Data i wanna use in the Stored Procedure

i Pass the temporary table Name to the Stored Procedure

But i couldnt use it with aCursor or even Normal Select Statement.

coz i have to concatinate it in the select Statemant

Searchin For Help

Thanks for all of u

|||Hi All.
This is one problem that kept on following me. I tried all the other suggestions and it worked fine at the time. Recently I decided to look for an alternative that will give me more flexibility.
My problem was I need to pass a list of keys to a stored proc (sp). The system I work on was architectured to work over slow networks and the internet, so no unnecessary call to the backend, hence my need to pass a list of keys.

I pass my keys in as a delimeted string (delimeter can be "," or "|" or any chosen character, this will be more clear later).
On the database I have a table-value function that convert this delimeted string to a table and this allows me to use it in joins and "where fk_somekey in (select * strval from delimtable)" statements.
Here is the function:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Lucian@.Probia.co.za
-- Create date: 2006/11/22
-- Description: Returns table for delimited string
-- =============================================
Create FUNCTION [dbo].[DelimStrToTable]
(
-- Add the parameters for the function here
@.delimStr varchar(8000),
@.delimchar char
)
RETURNS
@.StrValTable TABLE
(
-- Add the column definitions for the TABLE variable here
StrVal varchar(1000)

)
AS
BEGIN
-- Fill the table variable with the rows for your result set
declare @.strlist varchar(8000), @.pos int, @.delim char, @.lstr varchar(1000)
set @.strlist = @.delimStr
set @.delim = @.delimchar

while ((len(@.strlist) > 0) and (@.strlist <> ''))
begin
set @.pos = charindex(@.delim, @.strlist)

if @.pos > 0
begin
set @.lstr = substring(@.strlist, 1, @.pos-1)
set @.strlist = ltrim(substring(@.strlist,charindex(@.delim, @.strlist)+1, 8000))
end
else
begin
set @.lstr = @.strlist
set @.strlist = ''
end
Insert @.StrValTable values (@.lstr)
--print @.lstr
end

RETURN
END

and now I can do this:

Select * from Authors where AuthorId in (select convert(int,strval) from DelimStrToTable('1|2|11|45', '|'))

or
Select A.* from Authors A inner join DelimStrToTable('1|2|11|45', '|') as IDLst
on A.AuthorId = convert(int,IDLst.strval)

One is of course limited by the size of stored proc parameter. For most of my scenarios, this worked fine. Hope it serves as an alternative.

Lucian

|||I think the line
StrVal varchar(1000)
can be changed to
StrVal varchar(10)
The size here depend on the size of your list item in the parameter. Size of 10 is fine for a list of integers.