Error in Sql 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

General SQL Server Forums

New to SQL Server Programming

Error in Sql stored procedure

Author

Topic

Swati Jain
Posting Yak Master

139 Posts

Posted - 2007-03-26 : 08:40:32

error in stored procedure
an error or column name is missing
for SELECT INTO statement,verify each column has name,for other statements look for empty alias name alies defiened as "" or []
add a name or single space as the alias name,an object name or column is missing or empty

stored procedure
ALTER PROCEDURE dbo.spM3_ByLocationSearch

@DEPOTTRACK_CREATEDDATE VARCHAR(50) = NULL,
@depottrack_locid VARCHAR(50) = "",
@OrderBy VARCHAR(40) = NULL

AS
SET NOCOUNT ON
DECLARE @Select NVARCHAR(4000),
@Where NVARCHAR(4000),
@SqlFinal NVARCHAR(4000),
@ErrorMsgID INT,
@ErrorMsg VARCHAR(200)
SET @Select=''

SET @Where=''

SET @Select = 'WITH SearchLocList AS
(select * from
(
SELECT dp.depottrack_inventory_serial_number
FROM depottrack DP, (SELECT depottrack_inventory_serial_number
,MAX(DEPOTTRACK_CREATEDDATE) AS DEPOTTRACK_CREATEDDATE
FROM depottrack

) a
left join
(
SELECT
I.INVENTORY_SERIAL_NUMBER AS CONTAINER#,
product_class+ ' | ' + product_description as SIZE | TYPE,
inventory_status as STATUS,
inventory_vendor_id as VENDOR,
ISNULL(INVENTORY_COST_PURCHASE,0) AS PURCHASE PRICE,
ISNULL(INVENTORY_COST_UPFIT,0) + ISNULL(INVENTORY_COST_MATERIAL,0) AS UPFIT,
ISNULL(INVENTORY_COST_DRAYAGE,0) AS DRAYAGE,
ISNULL(INVENTORY_COST_PURCHASE,0) + ISNULL(INVENTORY_COST_UPFIT,0) +
ISNULL(INVENTORY_COST_MATERIAL,0) + ISNULL(INVENTORY_COST_DRAYAGE,0)
AS TOTAL
from
INVENTORY I LEFT JOIN VW_IV_COST_PURCHASE P on I .INVENTORY_GUID =
P.INVENTORY_GUID
LEFT JOIN VW_IV_COST_UPFIT U on I .INVENTORY_GUID = U.INVENTORY_GUID
LEFT JOIN VW_IV_COST_DRAYAGE D on I .INVENTORY_GUID = D.INVENTORY_GUID
LEFT JOIN VW_IV_COST_MATERIAL M on I .INVENTORY_GUID = M.INVENTORY_GUID
left join product on i.inventory_product_id = product.product_id
)
b
on a.depottrack_inventory_serial_number = b.CONTAINER#
where container# is not null
order by depottrack_inventory_serial_number )
Select * from SearchLocList'

IF (@DEPOTTRACK_CREATEDDATE IS NOT NULL)
BEGIN
IF (@Where <>'' )

SET @Where =@Where + 'SearchLocList.DEPOTTRACK_CREATEDDATE <=''' + CONVERT(VARCHAR, @DEPOTTRACK_CREATEDDATE,101) + ''''

ELSE
SET @Where = 'SearchLocList.DEPOTTRACK_CREATEDDATE <= ''' + CONVERT(VARCHAR,@DEPOTTRACK_CREATEDDATE,101) + ''''
END

/*CONVERT(VARCHAR(10),DP.depottrack_createddate,101) <= */

IF(@depottrack_locid<>"")
BEGIN
IF (@Where <>'' )
SET @Where =@Where + ' AND SearchLocList.depottrack_locid=''' + @depottrack_locid + ''''
ELSE
SET @Where = 'AND SearchLocList.depottrack_locid =''' + @depottrack_locid+ ''''
END

IF ((@DEPOTTRACK_CREATEDDATE IS NOT NULL))
BEGIN
IF (@Where <>'' )

SET @Where =@Where + 'SearchLocList. CONVERT(VARCHAR(10),DP.depottrack_createddate,101) <=''' + CONVERT(VARCHAR, @DEPOTTRACK_CREATEDDATE,101) + ''''

ELSE
SET @Where = 'SearchLocList. CONVERT(VARCHAR(10),DP.depottrack_createddate,101)<=''' + CONVERT(VARCHAR,@DEPOTTRACK_CREATEDDATE,101) + ''''
END

IF (@OrderBy IS NULL)
BEGIN
SET @OrderBy= 'SearchLocList.DP.depottrack_createddate, DP.depottrack_inventory_serial_number'
END

IF (@Where <> '')
BEGIN
SET @SqlFinal= @Select + ' where ' + @Where + ' order by ' + @OrderBy
END
ELSE
BEGIN
SET @SqlFinal= @Select + ' order by ' + @ORDERBY
END

PRINT @SqlFinal

EXEC SP_EXECUTESQL @SqlFinal

RETURN

nr
SQLTeam MVY

12543 Posts

Posted - 2007-03-26 : 09:00:24

Try displaying the executed string and you'll probably spot the problem.

suspect
IF (@Where <>'' )

SET @Where =@Where + 'SearchLocList.DEPOTTRACK_CREATEDDATE <=''' + CONVERT(VARCHAR, @DEPOTTRACK_CREATEDDATE,101) + ''''

s.b.
IF (@Where <>'' )

SET @Where =@Where + ' and SearchLocList.DEPOTTRACK_CREATEDDATE <=''' + CONVERT(VARCHAR, @DEPOTTRACK_CREATEDDATE,101) + ''''

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-26 : 09:24:29

I can't see the use of dynamic sql here.
Also I can spot a missing GROUP BY and a invalid column alias.

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-26 : 09:55:09

You've got to be kidding

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources