Syntex Error in Stored procedure

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)

Syntex Error in Stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-11-08 : 07:21:58

yamadootha writes "What is wrong with the syntex?

CREATE PROCEDURE [dbo].[sp_SearchRecords]
(
@whereclause as varchar(500)
)
AS
SET QUOTED_IDENTIFIER OFF

--Declare @SQL VarChar(1000)
Declare @OrderBy varchar(50)
SET @OrderBy = ' order by Field1 DESC'
--Declare @DollarSign varchar(2)
--SET @DollarSign = '$'

SELECT CONVERT(char(10),Date1,1) as Date1,CONVERT(char(10),Date2,1) as Date2,Field1,Field2,Field3,
'$' + CONVERT(NCHAR(15),cast(Field4 as money),1) as Field4
FROM TableName WHERE + @whereclause + @OrderBy

GO"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-08 : 07:39:49

quote:
Originally posted by AskSQLTeam

yamadootha writes "What is wrong with the syntex?

CREATE PROCEDURE [dbo].[sp_SearchRecords]
(
@whereclause as varchar(500)
)
AS
SET QUOTED_IDENTIFIER OFF

--Declare @SQL VarChar(1000)
Declare @OrderBy varchar(50)
SET @OrderBy = ' order by Field1 DESC'
--Declare @DollarSign varchar(2)
--SET @DollarSign = '$'

SELECT CONVERT(char(10),Date1,1) as Date1,CONVERT(char(10),Date2,1) as Date2,Field1,Field2,Field3,
'$' + CONVERT(NCHAR(15),cast(Field4 as money),1) as Field4
FROM TableName WHERE + @whereclause + @OrderBy

GO"

your query, use dynamic sql for this.

--------------------
keeping it simple...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-08 : 07:40:59

Think about it
select *
from tbl
where + 'hello'

does that make sense?

declare @sql varchar(8000)
select @sql = 'SELECT CONVERT(char(10),Date1,1) as Date1,CONVERT(char(10),Date2,1) as Date2,Field1,Field2,Field3,
''$'' + CONVERT(NCHAR(15),cast(Field4 as money),1) as Field4
FROM TableName WHERE ' + @whereclause + @OrderBy
exec (@sql)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources