Solved

add variable to called sql code

Posted on 2011-09-15
4
231 Views
Last Modified: 2012-05-12
I found a flaw in my sql procedure but I need to add a variable to it but I do not know how I can do this in the stored procedure.  The following is the stored procedure that call the intQid and sql and then loops through each sql statment to get results but I need to put the @Qid into the sql statemetn is there a way to do this??

First loop gets
@Qid     @Sql
  3           sql satement see example below
  4             ""
  5             ""

This is the Stored Procedure

BEGIN

declare @counter int, @countfilter int, @Sql nvarchar(4000), @PositionId int, @SSN varchar(10), @cur cursor, 
		@taskID int, @Qid int, @Posn_Data varchar(50), @Sold_Data varchar(50)
		
Declare Filter_Cursor CURSOR FOR Select	distinct mf.intQuestionId, q.strFilterSql
								From tblMobFilter mf INNER JOIN tblSRPQuestion q on 
								q.intQuestionId = mf.intQuestionId	
									
	OPEN Filter_Cursor

	FETCH NEXT FROM Filter_Cursor INTO @Qid,@Sql
	 
	while @@FETCH_STATUS =0 
		BEGIN		
			--- Call the SQL Filter and assign the unit # as the variable to get at table
			set @Sql = N'set @cur = cursor for ' + @Sql  + ';open @cur';

			exec sp_executesql @Sql, N'@cur cursor OUTPUT', @cur output;

			fetch next from @cur into @PositionId, @Posn_Data, @Sold_Data, @SSN;
			
			while @@FETCH_STATUS =0 
				BEGIN
					Insert Into tblPermTaskTemp (intQId, intPositionID, dtFound, strPosnData, strSoldData, strSSN) 
					VALUES (@Qid, @PositionId, Getdate(), @Posn_data, @Sold_Data, @SSN);
					
					fetch next from @cur into @PositionId, @Posn_Data, @Sold_Data, @SSN; 
				END
			close @cur;
			deallocate @cur;
	FETCH NEXT FROM Filter_Cursor INTO @Qid,@Sql;
		END 
	
END

Close Filter_Cursor
deallocate Filter_Cursor


This is an example sql that is called and where I need to put the @Qid to pull hte correct intUnitMobId's

Select p.intPositionId, 'Final Attendance', isnull(Convert(Varchar(12), a.dtTimein), 'No Record'), pp.strSSN from 
tblMobUnitPosition p INNER JOIN tblMobUnitPersonnel as pp on p.intPositionId = pp.intPositionId INNER JOIN tblMOBUnits as da 
On da.intUnitMobID = p.IntUnitMobID LEFT JOIN tblSRPAttendance a on pp.strssn = a.strSSN where ((a.dtTimeIn < DateADD(day, -90, da.dtDeploy)) 
or (isnull(Convert(Varchar(12), a.dtTimeIn), 'BAD') = 'BAD') ) And p.intUnitMobId IN 
(SELECT intUnitMobID from tblMobFilter where intQuestionId = PUT THE @Qid IN RIGHT HERE)

Open in new window

0
Comment
Question by:kdeutsch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 22

Accepted Solution

by:
pivar earned 500 total points
ID: 36545598
Hi,

If you use @Qid as a placeholder in @sql, like

(SELECT intUnitMobID from tblMobFilter where intQuestionId = @Qid)

you could do

                  --- Call the SQL Filter and assign the unit # as the variable to get at table
                  set @Sql = N'set @cur = cursor for ' + REPLACE(@Sql, '@Qid', CONVERT(VARCHAR(9), @Qid))  + ';open @cur';

/peter
0
 

Author Comment

by:kdeutsch
ID: 36545928
Pivar,
Ok will try this and see what happens.
0
 

Author Comment

by:kdeutsch
ID: 36550296
Pivar,
  Ok works greate thanks for the help.
0
 

Author Closing Comment

by:kdeutsch
ID: 36550297
thanks
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question