Stored Procedure - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

Stored Procedure

Author

Topic

adurstew
Starting Member

22 Posts

Posted - 2004-03-24 : 17:17:23

Hi, Seems that I wasn't able to fix the problem. Using the example below how can I use of the value of EXEC(@SQLStatement) in the stored procedure... for example print @SqlStatement

CREATE PROCEDURE MyProc
(@TableName varchar(255),
@FirstName varchar(50),
@LastName varchar(50))
AS

-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)

-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT Count(*) FROM " +
@TableName + "WHERE FirstName = '"
+ @FirstName + "' AND LastName = '"
+ @LastName + "'"

-- Execute the SQL statement
EXEC(@SQLStatement)

I need to print the returned value?

Thanks,
Andrew

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-24 : 18:16:20

Please do not create a duplicate thread.

From the answers that you have received in the other thread, you should realize what you are trying to do is very bad.

You should redesign and rethink what you are doing.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33690

Tara

adurstew
Starting Member

22 Posts

Posted - 2004-03-25 : 09:05:16

Sorry for posting another thread but I mentioned that I had fixed the issue in the other post, turns out this was not the case.

I don't understand why you would think this is a bad idea? It's either this or I have to make 3 ADO connections to the database to use a failover query. This way I can call one dynamic stored procedure and only have to take one trip to the database.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-25 : 09:15:04

So when your statement has returned the values, why don't you use your presentation layer printing capabilities? Or is this for some kind of debugging?

As always with dynamic sql, I suggest reading this article. http://www.sommarskog.se/dynamic_sql.html . Has been many times an eye-opener.

--Frank
http://www.insidesql.de

jhermiz

3564 Posts

Posted - 2004-03-25 : 09:27:04

from my reading I have always learned that SPROCS should not be used to return a value. The only value that should be returned is a status value of success or failure. Your SPROC should return a recordset, No ?

Jon

adurstew
Starting Member

22 Posts

Posted - 2004-03-25 : 09:29:55

quote:
Originally posted by jhermiz

from my reading I have always learned that SPROCS should not be used to return a value. The only value that should be returned is a status value of success or failure. Your SPROC should return a recordset, No ?

Jon

Yes, ultimatly it will return a recordset, however what I need do is a failover routing... for example...

Select * from Table Where field1=1 AND field2 = 2

If EMPTY Do this

Select * from Table Where field1 = 1

IF EMPTY Do This

Select * from Table

You see? I'm trying to run a failover routine.

jhermiz

3564 Posts

Posted - 2004-03-25 : 09:36:24

[code]
CREATE PROCEDURE MyProc
@TableName varchar(255),
@FirstName varchar(50),
@LastName varchar(50)
AS
BEGIN
SET NOCOUNT ON
SELECT Count(*) As SomeCount FROM
@TableName WHERE FirstName=@FirstName AND LastName=@LastName
Set NOCOUNT OFF
End
GO
[/code]

adurstew
Starting Member

22 Posts

Posted - 2004-03-25 : 09:40:16

quote:

Originally posted by jhermiz


CREATE PROCEDURE MyProc
@TableName varchar(255),
@FirstName varchar(50),
@LastName varchar(50)
AS
BEGIN
SET NOCOUNT ON
SELECT Count(*) As SomeCount FROM
@TableName WHERE FirstName=@FirstName AND LastName=@LastName
Set NOCOUNT OFF
End
GO

That gives an error wanting the variable @TableName to be declared... this is part of the problem i've been having.

jhermiz

3564 Posts

Posted - 2004-03-25 : 09:41:59

quote:
Originally posted by adurstew

quote:
Originally posted by jhermiz

CREATE PROCEDURE MyProc
@TableName varchar(255),
@FirstName varchar(50),
@LastName varchar(50)
AS
BEGIN
SET NOCOUNT ON
SELECT Count(*) As SomeCount FROM
@TableName WHERE FirstName=@FirstName AND LastName=@LastName
Set NOCOUNT OFF
End
GO

That gives an error wanting the variable @TableName to be declared... this is part of the problem i've been having.

Your client needs to pass the table name in as a parameter...or you hardcode that table name in.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-25 : 09:44:59

I might repeat myself, but you should really read this article http://www.sommarskog.se/dynamic_sql.html

It's free and won't bite

--Frank
http://www.insidesql.de

adurstew
Starting Member

22 Posts

Posted - 2004-03-25 : 09:45:32

quote:

Your client needs to pass the table name in as a parameter...or you hardcode that table name in.

It's no problem to pass in the value for @TableName but I can't save the stored procedure because it keeps giving me an error.

jhermiz

3564 Posts

Posted - 2004-03-25 : 09:59:28

quote:
Originally posted by adurstew

quote:

Your client needs to pass the table name in as a parameter...or you hardcode that table name in.

It's no problem to pass in the value for @TableName but I can't save the stored procedure because it keeps giving me an error.

My bad...

What about this havent tested:


CREATE PROCEDURE general_select 
@TableName varchar(255),
@FirstName varchar(50),
@LastName varchar(50)
AS
BEGIN
SET NOCOUNT ON
EXEC('SELECT Count(*) As SomeCount FROM' +
@TableName + ' WHERE FirstName=''' + @FirstName + ''' AND LastName=''' + @LastName + '''') 
Set NOCOUNT OFF
End
GO

My single quotes may be off...but it looks like you were just missing some spaces in your original select clause...remember to include a space between WHERE and AND's

Jon

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-25 : 12:41:26

Jon, it's ok to return parameters through stored procedures. You don't have to return a record set.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-25 : 12:42:02

Andrew, dynamic sql is bad for performance and for security. This is why it should be avoided.

Tara

adurstew
Starting Member

22 Posts

Posted - 2004-03-25 : 13:48:54

quote:
Originally posted by tduggan

Andrew, dynamic sql is bad for performance and for security. This is why it should be avoided.

Tara

Tara, it's either a dynamic sql script or 3 seperate connections to the database using ADO from ASP.

What i'm trying to get to is the following...

Select * from table1 where a = 1 and b = 2 and c = 3

if rows = 0 then
select * from table1 where a = 1 and b = 2

if rows = 0 then
select * from table1 where a = 1

Have any other ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-25 : 13:54:17

@@ROWCOUNT is what you need using what you just posted:


Select * from table1 where a = 1 and b = 2 and c = 3

if @@ROWCOUNT = 0
	select * from table1 where a = 1 and b = 2

if @@ROWCOUNT = 0
	select * from table1 where a = 1

Tara

adurstew
Starting Member

22 Posts

Posted - 2004-03-25 : 13:59:24

quote:
Originally posted by tduggan

@@ROWCOUNT is what you need using what you just posted:

Select * from table1 where a = 1 and b = 2 and c = 3

if @@ROWCOUNT = 0
select * from table1 where a = 1 and b = 2

if @@ROWCOUNT = 0
select * from table1 where a = 1

Tara

Tara, I have tried that and it returns 3 rowsets...that is one of the big issues... It seems that if the first one is empty then the second runs but then also the 3rd runs.

Also query analyzer gives me 1 empty and 2 filled rowsets... that's what I really don't understand.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-25 : 14:01:01

Then I'm not understanding what you want. Please show us some sample data and what you are receiving as your result set.

Tara

jhermiz

3564 Posts

Posted - 2004-03-25 : 14:02:41

Not sure what you are looking at either...your if's both execute..do you want one of them to? Maybe an explanation or sample data?

adurstew
Starting Member

22 Posts

Posted - 2004-03-25 : 14:04:25

quote:
Originally posted by tduggan

Then I'm not understanding what you want. Please show us some sample data and what you are receiving as your result set.

Tara

I can email you a screen capture of the Query Analyzer if you like. I'm sure that would be alot easier.

adurstew
Starting Member

22 Posts

Posted - 2004-03-25 : 14:05:49

Here is EXACTLY what i'm running as the stored procedure.


CREATE PROCEDURE spFailOver
	(@TableName varchar(255),
	@ProjectNumber varchar(16),
	@Language varchar(16) = 0,
	@Argument varchar(255) = "0")
AS

Declare @sql varchar(4000)

SET nocount on

--Project and language specific
if (@Argument="0")
	SELECT @sql = "SELECT * FROM " +
			@TableName + " WHERE Project_Number = '"
			+ @ProjectNumber + "' AND Language = " + @Language
else
	SELECT @sql = "SELECT * FROM " +
			@TableName + " WHERE Project_Number = '"
			+ @ProjectNumber + "' AND Language = " + @Language + " And "
			+ @Argument

EXEC(@sql)

--If no records found then fail over to the next procedure, otherwise just return the recordset
if @@RowCount = 0
	if @Argument = "0"
		SELECT @sql = "SELECT * FROM " +
			@TableName + " WHERE Language=" + @Language
			+ " AND (Project_Number='' OR Project_Number IS NULL)"
	else
		SELECT @sql = "SELECT * FROM " +
			@TableName + " WHERE Language=" + @Language
			+ " AND " + @Argument + " AND (Project_Number='' OR Project_Number IS NULL)"
EXEC(@sql)

--If no records found here then it will fail over to a default in the english language
if @@RowCount = 0
	if @Argument = "0"
		SELECT @sql = "SELECT * FROM " +
			@TableName + " WHERE Language=0"
			+ " AND (Project_Number='' OR Project_Number IS NULL)"
	else
		SELECT @sql = "SELECT * FROM " +
			@TableName + " WHERE Language=0"
			+ " AND " + @Argument + " AND (Project_Number='' OR Project_Number IS NULL)"
EXEC(@sql)
GO

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources