Error In Stored procedure -dynamic table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error In Stored procedure -dynamic table

Author

Topic

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-08-11 : 05:01:06

I am passing table name as dynamic to stored procedure. I am getting error in all if conditions[if exists (select 1 from @TblName WHERE ZipData = @Zipcode) ]. I am not knowing how to pass table name in if condtion in my stored procedure. Please any one can correct this stored proc?

CREATE PROCEDURE [dbo].[SPGetDataByZcode]
(
@Zipcode INT,
@Querystr Varchar(200),
@TblName Varchar(100)
)
AS
Begin
SET NOCOUNT ON

DECLARE @Result VARCHAR(MAX),
@ZcodeHigh INT,
@ZcodeLow INT,
@ZcodeToUse INT

if exists (select 1 from @TblName WHERE ZipData = @Zipcode)
Begin
Set @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM ' + @TblName + ' WHERE ZipData =' + Convert(varchar(30),@Zipcode) + ') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u1 ORDER BY theValue DESC'
End
else
Begin
If Len(@Zipcode) > 5
Begin
SET @ZcodeToUse = Convert(INT,LEFT(cast(@Zipcode as varchar(32)),len(@Zipcode) - 4))
SET @ZcodeHigh = @ZcodeToUse + 1
SET @ZcodeLow = @ZcodeToUse - 1

if exists (select 1 from ' + @TblName + ' WHERE ZipData = @ZcodeHigh)
Begin
SET @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM ' + @TblName + ' WHERE ZipData =' + Convert(varchar(30),@ZcodeHigh) + ') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u4 ORDER BY theValue DESC'
End
else if exists (select 1 from ' + @TblName + ' WHERE ZipData = @ZcodeLow)
Begin
SET @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM ' + @TblName + ' WHERE ZipData =' + Convert(varchar(30),@ZcodeLow) + ') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u5 ORDER BY theValue DESC'
END
END
-- If there is no hypen in zipcode. Check by adding +1 and -1
Else If Len(@Zipcode) <= 5
BEGIN
SET @ZcodeHigh = @Zipcode + 1
SET @ZcodeLow = @Zipcode - 1

If exists (select 1 from ' + @TblName + ' WHERE ZipData = @ZcodeHigh)
Begin
SET @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM ' + @TblName + ' WHERE ZipData =' + Convert(varchar(30),@ZcodeHigh) + ') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u2 ORDER BY theValue DESC'
End
else if exists (select 1 from ' + @TblName + ' WHERE ZipData = @ZcodeLow)
Begin
SET @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM ' + @TblName + ' WHERE ZipData =' + Convert(varchar(30),@ZcodeLow) + ') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u3 ORDER BY theValue DESC'
END
END
End

Exec(@Result)
--Print @Result
End

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-11 : 05:59:06

u cannot use the @TblName in the if clause u should use dynamic sql in if exists statement

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-08-11 : 06:02:39

I have done with some changes..Again getting error at if statement. can any one modify this?

ALTER PROCEDURE [dbo].[SPGetCountryDataByZcode]
(
@Zipcode INT,
@Querystr Varchar(200),
@TblName Varchar(100)
)
AS
Begin
SET NOCOUNT ON

DECLARE @Result VARCHAR(MAX),
@ZcodeHigh INT,
@ZcodeLow INT,
@ZcodeToUse INT,
@CondStmt1 varchar(200),
@CondStmt2 varchar(200),
@CondStmt3 varchar(200),
@CondStmt4 varchar(200),
@CondStmt5 varchar(200)

Set @CondStmt1 = 'select 1 from ' + @TblName + ' WHERE ZipData = ' + Convert(varchar(30),@Zipcode)

if exists (@CondStmt1)
Begin
Set @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM ' + @TblName + ' WHERE ZipData =' + Convert(varchar(30),@Zipcode) + ') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u1 ORDER BY theValue DESC'
End
else
Begin
If Len(@Zipcode) > 5
Begin
SET @ZcodeToUse = Convert(INT,LEFT(cast(@Zipcode as varchar(32)),len(@Zipcode) - 4))
SET @ZcodeHigh = @ZcodeToUse + 1
SET @ZcodeLow = @ZcodeToUse - 1

Set @CondStmt2 = 'select 1 from ' + @TblName + ' WHERE ZipData = ' + Convert(varchar(30),@ZcodeHigh)
Set @CondStmt3 = 'select 1 from ' + @TblName + ' WHERE ZipData = ' + Convert(varchar(30),@ZcodeLow)

if exists (@CondStmt2)
Begin
SET @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM ' + @TblName + ' WHERE ZipData =' + Convert(varchar(30),@ZcodeHigh) + ') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u4 ORDER BY theValue DESC'
End
else if exists (@CondStmt3)
Begin
SET @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM ' + @TblName + ' WHERE ZipData =' + Convert(varchar(30),@ZcodeLow) + ') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u5 ORDER BY theValue DESC'
END
END
-- If there is no hypen in zipcode. Check by adding +1 and -1
Else If Len(@Zipcode) <= 5
BEGIN
SET @ZcodeHigh = @Zipcode + 1
SET @ZcodeLow = @Zipcode - 1

Set @CondStmt4 = 'select 1 from ' + @TblName + ' WHERE ZipData = ' + Convert(varchar(30),@ZcodeHigh)
Set @CondStmt5 = 'select 1 from ' + @TblName + ' WHERE ZipData = ' + Convert(varchar(30),@ZcodeLow)

If exists (@CondStmt4)
Begin
SET @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM ' + @TblName + ' WHERE ZipData =' + Convert(varchar(30),@ZcodeHigh) + ') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u2 ORDER BY theValue DESC'
End
else if exists (@CondStmt5)
Begin
SET @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM ' + @TblName + ' WHERE ZipData =' + Convert(varchar(30),@ZcodeLow) + ') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u3 ORDER BY theValue DESC'
END
END
End

Exec(@Result)
--Print @Result
End

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-11 : 06:50:39

Set @CondStmt1 = 'select 1 from ' + @TblName + ' WHERE ZipData = ' + Convert(varchar(30),@Zipcode)
exec (@CondStmt1)

if (select @@rowcount) >0

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-08-12 : 03:50:47

But if i do like this, two exec statements(exec (@CondStmt1), exec(@Result)) are running in stored procedure. But i need only one statement to execute in SP.

quote:
Originally posted by bklr

Set @CondStmt1 = 'select 1 from ' + @TblName + ' WHERE ZipData = ' + Convert(varchar(30),@Zipcode)
exec (@CondStmt1)

if (select @@rowcount) >0

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-13 : 05:44:24

MNG:

Read this. http://www.sommarskog.se/dynamic_sql.html

and I don't mean -- glance at this once. Read it a few times. It will answer *all* the questions you have about this topic.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-08-13 : 07:01:06

Hi Charlie, thanks for your suggestion. I read this article long back. I know the concepts of dynamic Sql etc. But there the situation is different. the storedprocedure contains two exec statements. To avoid this, what i have to do in first exec statement. to understand easily i am giving sample stored procedure. Right now by using below Stored proc i am getting two results(bcz of two exec statements). i want to execute only final execution statement(Exec(@Result)).

ALTER PROCEDURE [dbo].[GetCountryDataByZcode_New]
(
@Zipcode INT,
@Querystr Varchar(200),
@TblName Varchar(100)
)
AS
Begin
SET NOCOUNT ON

DECLARE @Result VARCHAR(MAX),
@CondStmt1 Varchar(200)

Set @CondStmt1 = 'select 1 from ' + @TblName + ' WHERE ZipData = ' + Convert(varchar(30),@Zipcode)
exec (@CondStmt1)

if (select @@rowcount) >0
Begin
Set @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM ' + @TblName + ' WHERE ZipData =' + Convert(varchar(30),@Zipcode) + ') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u1 ORDER BY theValue DESC'
End
else
Begin
Set @Result = 'SELECT TOP 1 FROM sampletable'
End

Exec(@Result)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-13 : 07:08:33

So you read the article? I suggest you read it again. read it *better*

Especially check out EXEC sp_executeSql which you should be using anyway rather than EXEC(@sql) for a whole host of reasons.

You can execute your first dynamic call with a return variable using OUTPUT parameters.

Then decide what to do based on that rather than using @@ROWCOUNT This will mean that your first dynamic all won't return any results, it will just set a variable.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-08-13 : 07:36:56

Hi Charlie, I understood the concept which u mentioned. But when i try to implement getting same 2 outputs. Can you look in to the modified stored proc and make a changes needed for that.
CREATE PROCEDURE [dbo].[SGX_SP_GetCountryDataByZcode_New1]
(
@Zipcode INT,
@Querystr Varchar(200),
@TblName Varchar(100),
@RecordCnt INT OUTPUT
)
AS
Begin
SET NOCOUNT ON

DECLARE @Result VARCHAR(MAX),
@ZcodeHigh INT,
@ZcodeLow INT,
@ZcodeToUse INT,
@CondStmt1 varchar(200)

Set @CondStmt1 = 'select 1 from ' + @TblName + ' WHERE ZipData = ' + Convert(varchar(30),@Zipcode)
EXEC sp_executesql @CondStmt1

if (@RecordCnt >0 )
Begin
Set @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM ' + @TblName + ' WHERE ZipData =' + Convert(varchar(30),@Zipcode) + ') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u1 ORDER BY theValue DESC'
End
Begin
Set @Result = 'SELECT TOP 1 FROM sampletable'
End

Exec(@Result)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-13 : 08:07:20

I was meaning something like this:


DECLARE @existsZip INT

Set @CondStmt1 = 'SELECT @ex = COUNT(1) from ' + @TblName + ' WHERE ZipData = ' + Convert(varchar(30),@Zipcode) 
EXEC sp_executesql
	@CondStmt1
	, N'@ex INT OUTPUT'
	, @existsZip OUTPUT 

if (@existsZip > 0)
Begin 
Set @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM ' + @TblName + ' WHERE ZipData =' + Convert(varchar(30),@Zipcode) + ') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u1 ORDER BY theValue DESC' 
End 
Begin 
Set @Result = 'SELECT TOP 1 FROM sampletable'
End

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-08-13 : 08:39:42

I got what i am looking for. Thank you Charlie

quote:
Originally posted by Transact Charlie

I was meaning something like this:

DECLARE @existsZip INT

Set @CondStmt1 = 'SELECT @ex = COUNT(1) from ' + @TblName + ' WHERE ZipData = ' + Convert(varchar(30),@Zipcode)
EXEC sp_executesql
@CondStmt1
, N'@ex INT OUTPUT'
, @existsZip OUTPUT

if (@existsZip > 0)
Begin
Set @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM ' + @TblName + ' WHERE ZipData =' + Convert(varchar(30),@Zipcode) + ') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u1 ORDER BY theValue DESC'
End
Begin
Set @Result = 'SELECT TOP 1 FROM sampletable'
End

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources