Error in this stored procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

Error in this stored procedure?

Author

Topic

siekwel
Starting Member

1 Post

Posted - 2003-01-14 : 06:49:28

Hi,
I have this stored procedure for searching data.
Input are four parameters, on which should be search if not empty.
I receive errormessages : x not a column name, with x the content of an input parameter?

Does somebody recognize the failure?
thnx in advance...
B

ALTER PROCEDURE st_pr_search

(
@Specified_material varchar(50),
@UN_number varchar(20),
@ProductName varchar(40),
@SupplierName varchar(50)

/*@parameter2 datatype OUTPUT*/
)

AS

Declare @SQL VarChar(1000)

Select @SQL = 'SELECT Material.Specified_material, Products.ProductName, Suppliers.SupplierName '

Select @SQL = @SQL + 'FROM Material '

Select @SQL = @SQL + 'INNER JOIN Suppliers ON Material.SupplierID=Suppliers.SupplierID '

Select @SQL = @SQL + 'INNER JOIN Products ON Material.ProductID=Products.ProductID '

Select @SQL = @SQL + 'WHERE 1=1 '

IF NOT(@ProductName='' or @ProductName IS NULL)
BEGIN
Select @SQL = @SQL + 'AND Products.ProductName= ' + @ProductName
END

IF NOT(@Specified_material='' or @Specified_material IS NULL)
BEGIN
Select @SQL = @SQL + ' AND Material.Specified_material= ' + @Specified_material
END

IF NOT(@UN_number='' or @UN_number IS NULL)
BEGIN
Select @SQL = @SQL + ' AND Products.UN_number= ' + @UN_number
END

IF NOT(@SupplierName='' or @SupplierName IS NULL)
BEGIN
Select @SQL = @SQL + ' AND Suppliers.SupplierName=' + @SupplierName
END

Exec ( @SQL)

/* SET NOCOUNT ON */
RETURN

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-14 : 06:51:31

For string variables

Select @SQL = @SQL + ' AND Suppliers.SupplierName=''' + @SupplierName + ''''

If you print out the string you are executing you will see the problem.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

afterburn
Starting Member

28 Posts

Posted - 2003-01-17 : 11:22:25

First off the select @SQL = is a record set. If you query this in ADO you will get a recordset that has multiple recordsets in it.

If you do not need them I would suggest that you use

SET @SQL = ''

other than that you have to double quote the data. If you need to quote data for string literals then SQL requires that you place 2 like so

Select * from tbl where field = 'she''s'
SQL will look at it like a string literal.

Looking at you sp I find that you will have about 4 errors first off you sp does not allow null values passed to it. which means that you do not need to check the value

if not (@ProductName not is null and @ProductName <> '')

this statement could be like so

if @ProductName is not null
begin
select 0
select

Edited by - afterburn on 01/17/2003 11:25:50

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2003-01-17 : 21:12:55

quote:

First off the select @SQL = is a record set. If you query this in ADO you will get a recordset that has multiple recordsets in it.

If you do not need them I would suggest that you use

SET @SQL = ''

afterburn,

I've used

select @a = 'hello'

or some such many times and it's never returned a recordset for that.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources