Looping over an Incoming Comma Delimited List

There are two separe issues I have I would like help with. In both cases I'm
bringing into a stored procedure a comma delimited list of numeric values.
The length of the list varies.
1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
list).
Currently the IN statement does not want to regonized the list as a valid
list.
2. I need to loop over an incomming list of ids and select the name, and
insert it into a temp table. The problem I'm having is looping over the list
itself and getting the value in the next position to use in the select
statement. I can not use cursors or any other method that is resource heavy,
I have too many users using the same server and database at the same time.
Thanks
This should work for you. (I forgot where I got this from, but I am not the
original author)..
Create the function below first.
Create procedure myProc
@.inputValues varchar(100)
,@.SplitChar char(1)
as
Update tablex
set col1 = something
where col2 in (select col2 from fnIntSplitter(@.inputValues,@.SplitChar)
CREATE Function fnIntSplitter (@.IDs Varchar(100),@.SplitChar char(1) )
Returns @.Tbl_IDs Table (ID Int) As
Begin
-- Append comma
Set @.IDs = @.IDs + @.SplitChar
-- Indexes to keep the position of searching
Declare @.Pos1 Int
Declare @.pos2 Int
-- Start from first character
Set @.Pos1=1
Set @.Pos2=1
While @.Pos1<Len(@.IDs)
Begin
Set @.Pos1 = CharIndex(@.SplitChar,@.IDs,@.Pos1)
Insert @.Tbl_IDs Select Cast(Substring(@.IDs,@.Pos2,@.Pos1-@.Pos2) As Int)
-- Go to next non comma character
Set @.Pos2=@.Pos1+1
-- Search from the next charcater
Set @.Pos1 = @.Pos1+1
End
Return
End
"Alyx" <Alyx@.discussions.microsoft.com> wrote in message
news:BB8F2164-17CB-4E24-87EB-0B83AD647F10@.microsoft.com...
> There are two separe issues I have I would like help with. In both cases
> I'm
> bringing into a stored procedure a comma delimited list of numeric values.
> The length of the list varies.
> 1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
> list).
> Currently the IN statement does not want to regonized the list as a valid
> list.
> 2. I need to loop over an incomming list of ids and select the name, and
> insert it into a temp table. The problem I'm having is looping over the
> list
> itself and getting the value in the next position to use in the select
> statement. I can not use cursors or any other method that is resource
> heavy,
> I have too many users using the same server and database at the same time.
> Thanks

Looping over an Incoming Comma Delimited List

There are two separe issues I have I would like help with. In both cases I'm
bringing into a stored procedure a comma delimited list of numeric values.
The length of the list varies.
1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
list).
Currently the IN statement does not want to regonized the list as a valid
list.
2. I need to loop over an incomming list of ids and select the name, and
insert it into a temp table. The problem I'm having is looping over the list
itself and getting the value in the next position to use in the select
statement. I can not use cursors or any other method that is resource heavy,
I have too many users using the same server and database at the same time.
ThanksThis should work for you. (I forgot where I got this from, but I am not the
original author)..
Create the function below first.
Create procedure myProc
@.inputValues varchar(100)
,@.SplitChar char(1)
as
Update tablex
set col1 = something
where col2 in (select col2 from fnIntSplitter(@.inputValues,@.SplitChar)
CREATE Function fnIntSplitter (@.IDs Varchar(100),@.SplitChar char(1) )
Returns @.Tbl_IDs Table (ID Int) As
Begin
-- Append comma
Set @.IDs = @.IDs + @.SplitChar
-- Indexes to keep the position of searching
Declare @.Pos1 Int
Declare @.pos2 Int
-- Start from first character
Set @.Pos1=1
Set @.Pos2=1
While @.Pos1<Len(@.IDs)
Begin
Set @.Pos1 = CharIndex(@.SplitChar,@.IDs,@.Pos1)
Insert @.Tbl_IDs Select Cast(Substring(@.IDs,@.Pos2,@.Pos1-@.Pos2) As Int)
-- Go to next non comma character
Set @.Pos2=@.Pos1+1
-- Search from the next charcater
Set @.Pos1 = @.Pos1+1
End
Return
End
"Alyx" <Alyx@.discussions.microsoft.com> wrote in message
news:BB8F2164-17CB-4E24-87EB-0B83AD647F10@.microsoft.com...
> There are two separe issues I have I would like help with. In both cases
> I'm
> bringing into a stored procedure a comma delimited list of numeric values.
> The length of the list varies.
> 1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
> list).
> Currently the IN statement does not want to regonized the list as a valid
> list.
> 2. I need to loop over an incomming list of ids and select the name, and
> insert it into a temp table. The problem I'm having is looping over the
> list
> itself and getting the value in the next position to use in the select
> statement. I can not use cursors or any other method that is resource
> heavy,
> I have too many users using the same server and database at the same time.
> Thanks

Looping over an Incoming Comma Delimited List

There are two separe issues I have I would like help with. In both cases I'm
bringing into a stored procedure a comma delimited list of numeric values.
The length of the list varies.
1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
list).
Currently the IN statement does not want to regonized the list as a valid
list.
2. I need to loop over an incomming list of ids and select the name, and
insert it into a temp table. The problem I'm having is looping over the list
itself and getting the value in the next position to use in the select
statement. I can not use cursors or any other method that is resource heavy,
I have too many users using the same server and database at the same time.
ThanksThis should work for you. (I forgot where I got this from, but I am not the
original author)..
Create the function below first.
Create procedure myProc
@.inputValues varchar(100)
,@.SplitChar char(1)
as
Update tablex
set col1 = something
where col2 in (select col2 from fnIntSplitter(@.inputValues,@.SplitChar)
CREATE Function fnIntSplitter (@.IDs Varchar(100),@.SplitChar char(1) )
Returns @.Tbl_IDs Table (ID Int) As
Begin
-- Append comma
Set @.IDs = @.IDs + @.SplitChar
-- Indexes to keep the position of searching
Declare @.Pos1 Int
Declare @.pos2 Int
-- Start from first character
Set @.Pos1=1
Set @.Pos2=1
While @.Pos1<Len(@.IDs)
Begin
Set @.Pos1 = CharIndex(@.SplitChar,@.IDs,@.Pos1)
Insert @.Tbl_IDs Select Cast(Substring(@.IDs,@.Pos2,@.Pos1-@.Pos2) As Int)
-- Go to next non comma character
Set @.Pos2=@.Pos1+1
-- Search from the next charcater
Set @.Pos1 = @.Pos1+1
End
Return
End
"Alyx" <Alyx@.discussions.microsoft.com> wrote in message
news:BB8F2164-17CB-4E24-87EB-0B83AD647F10@.microsoft.com...
> There are two separe issues I have I would like help with. In both cases
> I'm
> bringing into a stored procedure a comma delimited list of numeric values.
> The length of the list varies.
> 1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
> list).
> Currently the IN statement does not want to regonized the list as a valid
> list.
> 2. I need to loop over an incomming list of ids and select the name, and
> insert it into a temp table. The problem I'm having is looping over the
> list
> itself and getting the value in the next position to use in the select
> statement. I can not use cursors or any other method that is resource
> heavy,
> I have too many users using the same server and database at the same time.
> Thankssql

Looking into all the tables of a schema

Hi everybody,
Is there any method using which I can search some numbers in all
numeric fields in all the tables of a particular schema '
This is urgent. Thnaks in advance.Ratan wrote:
> Hi everybody,
> Is there any method using which I can search some numbers in all
> numeric fields in all the tables of a particular schema '
>
> This is urgent. Thnaks in advance.
>
Possible? YEs... easy? Not so much... basically you'd have to create a
list of all tables in thedata base, loop through it, then look at all of
the columns in that table, find ones that match one type (you'd have to
know what that is in advance), then build a SQL statement that would
look at those fields for your value and return the results...
-ca|||Ratan
I think Aaron wrote this script which will give you an idea
CREATE PROCEDURE [dbo].[listTableColumns]
@.table SYSNAME
AS
BEGIN
SET NOCOUNT ON
DECLARE @.tid INT,
@.is VARCHAR(32),
@.ii VARCHAR(32),
@.ic VARCHAR(32)
SELECT
@.tid = OBJECT_ID(@.table),
@.is = CAST(IDENT_SEED(@.table) AS VARCHAR(32)),
@.ii = CAST(IDENT_INCR(@.table) AS VARCHAR(32)),
@.ic = CAST(IDENT_CURRENT(@.table) AS VARCHAR(32))
CREATE TABLE #pkeys
(
t_q SYSNAME, t_o SYSNAME, t_n SYSNAME,
cn SYSNAME, ks INT, pn SYSNAME
)
INSERT #pkeys EXEC sp_pkeys @.table
CREATE TABLE #sc
(
cn SYSNAME, formula NVARCHAR(2048)
)
INSERT #sc SELECT
cl.name, sc.text
FROM syscolumns cl
LEFT JOIN syscomments sc
ON cl.id = sc.id AND sc.number = cl.colid
WHERE cl.id = @.tid
SELECT
[COLUMN NAME] = i_s.column_name,
[DATA TYPE] = UPPER(DATA_TYPE)
+ CASE WHEN DATA_TYPE IN ('NUMERIC', 'DECIMAL') THEN
'(' + CAST(NUMERIC_PRECISION AS VARCHAR)
+ ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'
ELSE '' END
+ CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsIdentity')
WHEN 1 THEN
' IDENTITY (' + @.is + ', ' + @.ii + ')' ELSE '' END
+ CASE RIGHT(DATA_TYPE, 4) WHEN 'CHAR' THEN
' ('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)+')' ELSE '' END
+ CASE IS_NULLABLE WHEN 'No' THEN ' NOT ' ELSE ' ' END
+ 'NULL' + COALESCE(' DEFAULT ' + SUBSTRING(COLUMN_DEFAULT,
2, LEN(COLUMN_DEFAULT)-2), ''),
[CURRENT IDENTITY] = CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsIdentity')
WHEN 1 THEN @.ic ELSE '' END,
[FORMULA] = CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsComputed')
WHEN 1 THEN (SELECT SUBSTRING(formula, 2, len(formula)-2)
FROM #sc WHERE cn=i_s.column_name)
ELSE '' END,
[PRIMARY KEY?] = CASE WHEN pk.cn IS NOT NULL THEN 'Yes' ELSE '' END,
[COLUMN DESCRIPTION] = COALESCE(s.value, '')
FROM
INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
#pkeys pk
ON
pk.cn = i_s.column_name
LEFT OUTER JOIN
sysproperties s
ON
s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
AND s.smallid = i_s.ORDINAL_POSITION
AND s.name = 'MS_Description'
WHERE
i_s.TABLE_NAME = @.table
ORDER BY
i_s.ORDINAL_POSITION
DROP TABLE #pkeys
DROP TABLE #sc
END
GO
Sample usage:
CREATE TABLE [dbo].[fakeTable]
(
[id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[dt] [smalldatetime] NOT NULL DEFAULT (getdate()),
[body] [varchar] (255) NOT NULL DEFAULT (''),
[email] [varchar] (255) NULL,
[y] AS (datepart(year,[dt]))
)
GO
EXEC listTableColumns 'faketable'
DROP TABLE fakeTable
"Ratan" <ratan.nitrkl@.gmail.com> wrote in message
news:b1d3b4e4-f3b1-42b3-a501-4156c5dd4857@.e10g2000prf.googlegroups.com...
> Hi everybody,
> Is there any method using which I can search some numbers in all
> numeric fields in all the tables of a particular schema '
>
> This is urgent. Thnaks in advance.
>