Recursive Stored Procedure
-
I have requirement where I need to create stored procedure which accepts States(Multiple) and StudentID(Single) .
User will input the set of State's and also mention a single student ID, so my procedure should bring the details of that studentID from different states.
Assuming the Data for different state is in each different database with same table name.
I am not able to figure out how to make the SP recursive on state by keeping the studentID constant.
Result set is somewhat looks like below.
Help on this is greatly appreciated.
Thanks,
VSP
-
Thank you Keith for your reply. Sorry for not providing the enough info.
CREATE PROCEDURE [dbo].[USP_Student_History_Search]
@State VARCHAR(2)
@StudentID INT
AS
SET NOCOUNT ON;
DECLARE @index INT
DECLARE @N INT =SELECT CHARINDEX(',',@State)
WHILE @index <= @N
BEGIN
SET @DB = N'Select
[State],
[StudentID],
[Marks]
FROM [Academics_'+@State+'].[dbo].[StudentsHistory]'
Declare @sp-2 table
( [State],
[StudentID],
[Marks]
)
END
INSERT INTO @sp-2 VALUES(SELECT FROM @DB)
If the user passes states - 'TX, MI, CA' then the query should loop over three states from 3 different databases one after other and pull the information.
and finally place it in a single table like the picture shown above.
help is greatly appreciated.
-
one of many ways:
if isnull(object_id('result'), 0 ) <> 0
drop table result
create table result ( state varchar(2), studentId varchar, marks varchar )
declare @db varchar(500)
DECLARE @index INT =0
DECLARE @N INT = len(@State)-len(replace( @State,',',''))
WHILE @index <= @N
BEGIN
SET @DB = N' insert into result Select
[State],
[StudentID],
[Marks]
FROM [Academics_'+left(@State,2)+'].[dbo].[StudentsHistory]'
if @index <= @n
set @state = right(@state, len(@state)-3)
exec @db
end
select * from result
-
Both of the above methods leave you wide open to SQL Injection attacks.
With that in mind, let me ask... are all of the Academics_xx tables identical in every way except for their name and content? If so, it would likely be much better to create a partitioned view using the tables and join to that single view using the splitup version of the states parameter, instead.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you SrcName for your reply. I have followed your query and came up with this sample query. It was successfully created but when I am trying to execute, I am getting the below error.
Error Message:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
MyQuery:
CREATE PROCEDURE [dbo].[uspChannelHistory]
(@Channel_nbr INT
,@ChannelLevelID INT
,@ChannelName VARCHAR(50)
,@ChannelID INT
,@StateCode VARCHAR(60)
)
AS
SET NOCOUNT ON
IF OBJECT_ID('tempdb..##Result') IS NOT NULL
DROP TABLE ##Result
CREATE TABLE ##Result
(
Channel_nbrINT,
ChannelLevelIDINT,
ChannelNameVARCHAR(50),
ChannelIDINT,
StateCodeVARCHAR(2)
)
DECLARE @DB VARCHAR(500)
DECLARE @Count INT = 0
DECLARE @N INT = LEN(@StateCode) - LEN(REPLACE(@StateCode, ',', ''))
WHILE @Count <= @N
BEGIN
SET @DB = N'INSERT INTO ##Result
SELECT
Channel_nbr
ChannelLevelID
ChannelName
ChannelID
StateCode
FROM [Sample_'+ LEFT(@StateCode, 2) +'].[dbo].[StudentHistory]
WHERE Channel_nbr = @Channel_nbr
AND ChannelLevelID = @ChannelLevelID
AND ChannelName = @ChannelName
AND ChannelID = @ChannelID'
SET @Count = @Count + 1
IF@Count <= @N
SET @StateCode = RIGHT(@StateCode, LEN(@StateCode) - 3)
EXEC sp_executesql @DB, N'@Channel_nbr INT ,@ChannelLevelID INT ,@ChannelName VARCHAR(50),@ChannelID INT,@StateCode VARCHAR(60) ', @Channel_nbr, @ChannelLevelID, @ChannelName, @ChannelID, @StateCode
SELECT * FROM ##Result
END
--EXEC [dbo].[USP_Producer_History_Search] 2,4,'Media', 4, 'FL, TX'
-
Thank you Keith for prompt reply. I have changed the @DB to NVARCHAR.
SP Executed succeessfully but when I executed by passing parameters it throws error.
Msg 207, Level 16, State 1, Line 19
Invalid column name 'ChannelName'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'ChannelID'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'ChannelName'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'ChannelID'.
Msg 207, Level 16, State 1, Line 19
Invalid column name 'ChannelName'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'ChannelID'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'ChannelName'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'ChannelID'.
But I have those fields in the base table as well as I declared those in the temp table also.
-
looks like some missing commas
-
Not to mention that this is using global temp tables. What happens when you have two users using this proc? The first person's data will be truncated when the second person starts executing this. Add to that the sql injection vulnerability and it seems you need to revisit what you are trying to do here and find a more suitable approach.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Thank you Sean Lange, I did not think about it. I liked the idea of "Jeff Moden" but I am not sure about how to create a partitioned view.
I would really appreciate if someone can help me or direct me to a forum having similar kind of solution.
Thanks again.
-
Take a look at the following link on how to create, use and manage partitioned views:
http://technet.microsoft.com/en-us/library/ms190019(v=sql.105).aspx
-
Thank you very much Keith for your concern, I will look into this.
I am also trying to do a "UNION ALL" but not sure how to do it dynamically like...if a user passes 2 states it should do union all between 2 result sets and if he passes 3 states then it should do union all between three result sets.
thanks again Keith.
-
Thanks to all, I got this solved. I have assigned a GUID to the temp table name and built/select/drop it dynamically. I have tested it and it is working perfect.
Once again thanks everyone.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply