Need Some Help Here

Question

Post reply

Need Some Help Here

zaki-353237

Valued Member

Points: 52
More actions
December 12, 2006 at 11:13 am

#171566

I have write a procedure and i can compile it... no errors
But when i execute it, it give me an error...
here's my procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_CompStaging]
@DatabaseName sysname
AS
BEGIN
DECLARE @strSQLnvarchar(max)
DECLARE @strCntint
DECLARE @tabPNamesysname
DECLARE @tabSNamesysname
DECLARE @maxCountint
DECLARE @itemCounterint
SET @tabPName = @DatabaseName + '..tblBatch_01'
SET @tabSName = @DatabaseName + '..tblBatch_02'
SET @itemCounter = 1
WHILE 100 > 0 AND @itemCounter <= 100
BEGIN
SELECT @strSQL = ' 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 + ') '
+ ' 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 @strCnt = @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 = ' + @itemCounter
EXECUTE (@strSQL)
END
SET @itemCounter = @itemCounter + 1
END
END
and here the error message that i get
Msg 203, Level 16, State 2, Procedure SP_CompStaging, Line 63
The name ' SELECT count(*) FROM EPF_STG1..tblBatch_01 WHERE iID = 1 AND vchBatchNo IN (SELECT vchBatchNo FROM EPF_STG1..tblBatch_02) AND vchRecID IN (SELECT vchRecID FROM EPF_STG1..tblBatch_02) AND vchFiller IN (SELECT vchFiller FROM EPF_STG1..tblBatch_02) AND vchStateCode IN (SELECT vchStateCode FROM EPF_STG1..tblBatch_02) AND intTotalForm IN (SELECT intTotalForm FROM EPF_STG1..tblBatch_02) AND intTotalEmployerCont IN (SELECT intTotalEmployerCont FROM EPF_STG1..tblBatch_02) AND intTotalEmployeeCont IN (SELECT intTotalEmployeeCont FROM EPF_STG1..tblBatch_02) AND intHashTotal IN (SELECT intHashTotal FROM EPF_STG1..t' is not a valid identifier.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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?

zaki-353237 Valued Member Points: 52 More actions · Answer 2

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

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?

zaki-353237 Valued Member Points: 52 More actions · Answer 4

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

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.

zaki-353237 Valued Member Points: 52 More actions · Answer 6

Ok.. Will try that one.

Besides nvarchar, what else that i can use.

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Nothing else (for the command). For the input/output parameters you can also use numeric datatypes.

zaki-353237 Valued Member Points: 52 More actions · Answer 8

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

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!?!?

zaki-353237 Valued Member Points: 52 More actions · Answer 10

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Mabe this will help :

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=285646

zaki-353237 Valued Member Points: 52 More actions · Answer 12

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?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

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 !

zaki-353237 Valued Member Points: 52 More actions · Answer 14

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...