July 30, 2013 at 11:36 am
create table #temp
(
ID int,
Type nvarchar(10),
Value int
)
insert into #temp
values (1, 'A', '25')
insert into #temp
values (2, 'A', '47')
insert into #temp
values (3, 'B', '3')
insert into #temp
values (4, 'C', '3')
insert into #temp
values (5, 'D', '7')
-- use the same sql with type A and B , first only with A and then only with B
-- parameter/loop ?
-- do something with type A
-- START
SELECT ID, Type, Value + 1 from #temp where type = N'A'
-- more then one sqlquerys
-- cleanup
-- END
-- use the same sql with type B
-- START
SELECT ID, Type, Value + 1 from #temp where type = N'B'
-- more then one sqlquerys
-- cleanup
-- END
-- Do something else with type C and D
/Clas
July 30, 2013 at 11:51 am
well, i think you've abstracted the problem out to where we can't tell what you want.
why can't you do everything, in a single step, with SELECT ID, Type, Value + 1 from #temp(no WHERE statement at all)? why do you think you have to do something for each "type"(A,B,C,D,etc)?
your examples are only SELECTING, and i doubt you want to return Multiple Active Result Sets(MARS), so what are you really doing? updating?
the details you provide affect what solutions we can offer.
Lowell
July 30, 2013 at 12:59 pm
I want to run a section of sql queries with a specific type. Some types can use the same sql queries and the idea was to have the type as a parameter and create a loop.
We have 100 different types, many of them can use the same code. Although it is the same code they must be run each type separately.
run sql where Type = A
then run where Type = B
then run where Type = E
then run where Type = X
then run where Type = Y
.....
July 30, 2013 at 1:22 pm
I think what Lowell is saying is that you don't need a loop to do this. I understand you may want to perform different actions depending on the type, but there are also instances where you want to perform the identical operation on two or more types. A set based approach to this is going to be much more efficient than a giant loop with conditions for each type.
It would help us further help you if you could give us some examples of what you want to do. Writing a generic loop for you won't help things much. You say they can use the same code, but must be run separately, can you explain this a little better?
July 30, 2013 at 2:56 pm
With the assumption that you really do need to run a separate process for each type, then you will probably need to use dynamic SQL like this:
First the sample data:
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable
(
[ID] INT
,[Type] NVARCHAR(10)
,[Value] INT
)
INSERT INTO #TempTable
VALUES (1,'A','25')
INSERT INTO #TempTable
VALUES (2,'A','47')
INSERT INTO #TempTable
VALUES (3,'B','3')
INSERT INTO #TempTable
VALUES (4,'C','3')
INSERT INTO #TempTable
VALUES (5,'D','7')
--SELECT * FROM #TempTable AS tt
Now build and run the dynamic SQL:
DECLARE
@strSQL NVARCHAR(MAX)
,@strTypes NVARCHAR(4000)
--this creates a 2 dimensional string of the types/values from the table
SET @strTypes =
(SELECT
STUFF(
(SELECT
','+tt2.[Type]+'|'+CAST(tt2.[Value] AS NVARCHAR(5))
FROM
#TempTable AS tt2
FOR XML PATH('')),1,1,''))
SET @strSQL = ''
--now we can query the "array" and build the SELECT statements to execute
SELECT
@strSQL = @strSQL
+ 'SELECT '+CAST(t.N AS NVARCHAR(5))
+',[Type],'+dsk.Item2+'+1 AS [Value]
FROM #TempTable
WHERE [Type] = N'''+dsk.Item1+''';'+CHAR(10)+CHAR(13)
FROM
dbo.DelimitedSplit8K_2DIM(@strTypes,',','|') AS dsk
CROSS APPLY
dbo.Tally AS t
WHERE
dsk.ItemNumber = t.N
EXEC sp_executeSQL @strSQL
@strSQL builds a SELECT statement for each row in the result set and then executes the entire string without looping. The string that gets executed:
SELECT 1,[Type],25+1 AS [Value] FROM #TempTable WHERE [Type] = N'A'; SELECT 2,[Type],47+1 AS [Value] FROM #TempTable WHERE [Type] = N'A'; SELECT 3,[Type],3+1 AS [Value] FROM #TempTable WHERE [Type] = N'B'; SELECT 4,[Type],3+1 AS [Value] FROM #TempTable WHERE [Type] = N'C'; SELECT 5,[Type],7+1 AS [Value] FROM #TempTable WHERE [Type] = N'D';
You will need the following function to perform this which is a version of the standard DelimitedSplit8k but one that handles two dimensions. You will also need a Tally table which no database should be without!
CREATE FUNCTION [dbo].[DelimitedSplit8K_2DIM]
(
@pString VARCHAR(8000)
,@pDelimiter1 CHAR(1)
,@pDelimiter2 CHAR(1) = NULL
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter1 OR t.N = 0)
)
SELECT
ItemNumber
,Item1
,Item2 = REPLACE(Item2,Item1+@pDelimiter2,'')
FROM
(
SELECT
ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1)
,Item1 = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter2,@pString,s.N1),0)-s.N1,8000))
,Item2 = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter1,@pString,s.N1),0)-s.N1,8000))
FROM cteStart s
) i1
July 30, 2013 at 3:03 pm
clas (7/30/2013)
I want to run a section of sql queries with a specific type. Some types can use the same sql queries and the idea was to have the type as a parameter and create a loop.We have 100 different types, many of them can use the same code. Although it is the same code they must be run each type separately.
run sql where Type = A
then run where Type = B
then run where Type = E
then run where Type = X
then run where Type = Y
.....
still not quite enough details. when you say "run a section of sql queries", what is that going to do? select? insert? update? if it does a select, are the same columns going to be returned, even thought eh type changed?
if you can give concrete examples of what you want to do, we can surely help you.
Lowell
July 31, 2013 at 3:55 am
This is an example where a number of types using the same SQL.
Sql code must be easily transferred to MS Access,
SQL must be divided into smaller simple parts using the storage tables (# temp, MS Access is created/drop real tables, rankings are created with VB).
Sql code must be in the sql files and not in sql server procedures or functions.
Instead of that I must copy the same code over and over again to change/run different types, I would define the types to be run and loop through the defined types.
If the script gets a bit slow does not matter, it does not run very often.
------- START Type = N'Household'----------------
CREATE TABLE #rank
(
seq INT,
IndividualNo INT,
Start_DateNum int,
num int,
Next_DateNum int
)
insert into #rank
SELECT RANK() OVER (PARTITION BY IndividualNo ORDER BY Start_DateNum) as seq , IndividualNo, Start_DateNum, num,null
FROM Extract_Output
WHERE ((Type = N'Household'))
ORDER BY IndividualNo, Start_DateNum
UPDATE currow
SET
Next_DateNum = nextrow.Start_DateNum
FROM #rank currow
LEFT JOIN #rank nextrow ON currow.seq = nextrow.seq
- 1
AND currow.IndividualNo = nextrow.IndividualNo
UPDATE Extract_Output
SET Next_DateNum =#rank.Next_dateNum
FROM Extract_Output INNER JOIN
#rank ON Extract_Output.num = #rank.num
DROP TABLE #rank
------------- END ------------------
July 31, 2013 at 4:21 am
clas (7/31/2013)
...Sql code must be easily transferred to MS Access...
Why?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2013 at 4:54 am
The data must be handled in the same way regardless of what system you use.
Some are using MS SQL Server, others are using MS Access. The variables used later in longitudinal dataset to find the causes and context.
July 31, 2013 at 4:59 am
Are you processing mailing (DM) extracts?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2013 at 5:08 am
No
July 31, 2013 at 5:09 am
No
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply