Solved

add variable to called sql code

Posted on 2011-09-15
4
229 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
  • 3
4 Comments
 
LVL 22

Accepted Solution

by:
pivar earned 500 total points
Comment Utility
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
Comment Utility
Pivar,
Ok will try this and see what happens.
0
 

Author Comment

by:kdeutsch
Comment Utility
Pivar,
  Ok works greate thanks for the help.
0
 

Author Closing Comment

by:kdeutsch
Comment Utility
thanks
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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
Viewers will learn how the fundamental information of how to create a table.

728 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now