Need Some Help Here
-
I can't remote debug this from you. But I can tell you how I'd do it :
Replace the EXECUTE (@StrSQL) statement to
PRINT @StrSQL
Then copy / paste the results from the message pane and try to execute them in QA. Then you'll see what is the error in the dynamic SQL and you'll be able to fix it.
Also why do you think you need dynamic sql to solve this problem?
-
I have to use a dynamic sql to solve this probs cause im using a numbers of different staging database that will be created by the user.
so i just want to write one sp which can be used on all the staging database..
ive print out the @strSQL and and i can execute the statement in QA.. Can u tell me whether i can get the result of the EXEC statement in the SP. Can i assign the result of the EXEC statement to a variable. That will solve my prob..
Thnks
-
Something's not adding up here. If you can make the print statement execute [without altereations] then it should also work in the EXECUTE().
What do you mean exactly by putting the result into a variable? And what exactly is it you need to do with this proc / variable?
-
If i execute it.. EXECUTE (@strSQL)
It will return the results of this EXECUTE statement in QA. Things work fine there.
What I want to achieve here is, if the EXECUTE statement find a matching record, which in this SP it will return 1, I want to assign that '1' value to a variable so that I can use it in my IF statement below that EXECUTE statement.
What Im trying to do with this SP is, I will pass the staging database name to this procedure, and it will compare the records inside the tblBatch_01 and tblBatch_02. When it find and identical records, it will insert it in the production database. And this procedure is executed from the production database.
I cant figure out any better way to do this.
Thanks.
-
search this site for sp_executesql and output parameters. You should find what you need. The one draw back is that you have to use nvarchar which limits you to 4000 characters.
-
Ok.. Will try that one.
Besides nvarchar, what else that i can use.
Thanks
-
Nothing else (for the command). For the input/output parameters you can also use numeric datatypes.
-
THANKS A LOT!!!
It works now...
Wouldnt make it with out your help.
Heres how the code looks now
SELECT @strSQL = N' SELECT '+ @strCnt +' count(*) FROM ' + @tabPName +
+ ' WHERE iID = ' + cast(@itemCounter as varchar(5)) + '' +
+ ' AND vchBatchNo IN (SELECT vchBatchNo FROM ' + @tabSName + ') ' +
+ ' AND vchRecID IN (SELECT vchRecID FROM ' + @tabSName + ') '
+ ' AND vchFiller IN (SELECT vchFiller FROM ' + @tabSName + ') ' +
+ ' AND vchStateCode IN (SELECT vchStateCode FROM ' + @tabSName + ') ' +
+ ' AND intTotalForm IN (SELECT intTotalForm FROM ' + @tabSName + ') ' +
+ ' AND intTotalEmployerCont IN (SELECT intTotalEmployerCont FROM ' + @tabSName + ') ' +
+ ' AND intTotalEmployeeCont IN (SELECT intTotalEmployeeCont FROM ' + @tabSName + ') ' +
+ ' AND intHashTotal IN (SELECT intHashTotal FROM ' + @tabSName + ')'
EXECUTE sp_executesql @strSQL
IF (@strCnt) = 1
BEGIN
SELECT @strSQL = ' INSERT INTO EPF_PROD..tblBatch(vchBatchNo, vchRecID, vchFiller, vchStateCode, intTotalForm, intTotalEmployerCont, intTotalEmployeeCont, intHashTotal) ' +
+ ' SELECT vchBatchNo, vchRecID, vchFiller, vchStateCode, intTotalForm, intTotalEmployerCont, intTotalEmployeeCont, intHashTotal ' +
+ ' FROM @tabPName ' +
+ ' AND iID = ' + cast(@itemCounter as varchar(5))
EXECUTE (@strSQL)
END
-
I'm not sure I'm following here. Don't you want to save the output FROM the exec statement in @StrCnt? AFAIK this takes an output parameter that doesn't seem to be included in this code!?!?
-
Well... Yeah. I check the code again... And it doesnt work. Sorry.
And yes, I want to save the output from the EXEC statement in @strCnt
-
Mabe this will help :
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=285646
-
Im lost already.
I cant seems to find where can I put the output parameter..
Can you help me.
SELECT @strSQL = N' SELECT count(*) FROM ' + @tabPName +
+ ' WHERE iID = ' + cast(@itemCounter as varchar(5))
+ ' AND vchBatchNo IN (SELECT vchBatchNo FROM ' + @tabSName + ') ' +
+ ' AND vchRecID IN (SELECT vchRecID FROM ' + @tabSName + ') '
EXEC sp_executesql @strSQL, N'@strCntOut int output', @strCnt Output
This is how my codes looks like... And obviously it doesnt work. When I execute it, it returns two rows for me, 1 and 0. Whats that means?
-
Have you tried something like :
SELECT @strSQL = N'SET @strCntOut = (SELECT count(*) FROM ' + @tabPName +
+ ' WHERE iID = ' + cast(@itemCounter as varchar(5))
+ ' AND vchBatchNo IN (SELECT vchBatchNo FROM ' + @tabSName + ') ' +
+ ' AND vchRecID IN (SELECT vchRecID FROM ' + @tabSName + '))'
EXEC sp_executesql @strSQL, N'@strCntOut int output', @strCnt Output
Anyone feel free to jump in and help... I'm not exactly in my area of expertise here
! -
Finally...
I got it right this time. After looking through few examples in Microsft KB
Heres the code
WHILE 2 > 0 AND @itemCounter <= 2
BEGIN
SET @strSQL = N' SELECT @strCntOut = count(*) FROM ' + @tabPName +
+ ' WHERE iID = ' + cast(@itemCounter as varchar(5))
+ ' AND vchBatchNo IN (SELECT vchBatchNo FROM ' + @tabSName + ') ' +
+ ' AND vchRecID IN (SELECT vchRecID FROM ' + @tabSName + ')'
SET @Param = N'@strCntOutintOUTPUT'
EXECUTE sp_executesql @strSQL, @Param, @strCntOut = @strCnt OUTPUT
IF (@strCnt) = 1
BEGIN
SELECT @strSQL = ' INSERT INTO EPF_PROD..tblBatch(vchBatchNo, vchRecID, vchFiller, vchStateCode, intTotalForm, intTotalEmployerCont, intTotalEmployeeCont, intHashTotal) ' +
+ ' SELECT vchBatchNo, vchRecID, vchFiller, vchStateCode, intTotalForm, intTotalEmployerCont, intTotalEmployeeCont, intHashTotal ' +
+ ' FROM @tabPName ' +
+ ' AND iID = ' + cast(@itemCounter as varchar(5))
EXECUTE(@strSQL)
END
SET @itemCounter = @itemCounter + 1
END
Thanks for your help...
-
NP. Good luck with the rest of that project.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply