Solved

add variable to called sql code

Posted on 2011-09-15
4
233 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

ClickHouse in a General Analytical Workload

We have mentioned ClickHouse in some recent posts, where it showed excellent results.

In this article on Experts Exchange, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

628 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