Facing issue in SQL server 2008

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

Facing issue in SQL server 2008

Author

Topic

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2011-05-05 : 07:31:41

Hi I am using the following sp:

CREATE PROCEDURE [dbo].[xxxx]
(@LangID nvarchar(10),
@SortColumn varchar(50) = Null,
@UserID nvarchar(8) = Null)

AS
SET NOCOUNT ON

DECLARE @SELECT varchar(1000)
DECLARE @WHERE varchar(255)
DECLARE @ORDER varchar(50)

SELECT @WHERE = ''
SELECT @ORDER = ''

SELECT @SELECT = 'SELECT * '
SELECT @SELECT = @SELECT + ' FROM xxx ES as ES LEFT JOIN yyy SE on ES.esSectorID = SE.Code AND se.Language_Code=''' + @LangID
SELECT @SELECT = @SELECT + ''' LEFT JOIN MMM MT on ES.ID = MT.Code AND MT.Language_Code=''' + @LangID
SELECT @SELECT = @SELECT + ''' LEFT JOIN SSS as ss ON ES.esSiteID=ss.Code and ss.Language_Code=''' + @LangID
SELECT @SELECT = @SELECT + ''' LEFT JOIN VVV as VV ON ES.ID=VV.Code and vv.Language_Code=''' + @LangID + ''''

-- IF A USERS BID IS PASSED IN THEN USE IT
IF @UserID IS NOT NULL
SELECT @WHERE = ' WHERE ES.BID=''' + @UserID + ''''

-- IF A SORT WAS PASSED IN THEN USE IT
IF @SortColumn IS NOT NULL
SELECT @ORDER = ' ORDER BY ' + @SortColumn

EXEC(@SELECT + @WHERE + @ORDER)

but gives me the below Error message

An expression of non-boolean type specified in a context where a condition is expected, near 'WHERE'.

Could please help me...

kalyan Ashis Dey

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-05 : 07:36:38

Instead of exec, could you print @Select, @where and @order so we can see the statement that is being executed?

Jim

Everyday I learn something that somebody else already knew

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-05 : 07:37:48

wHEN YOU RUN YOUR SP IT ACTUALLY RUNS THIS

SELECT * FROM xxx ES as ES LEFT JOIN yyy SE on
ES.esSectorID = SE.Code AND se.Language_Code='1' LEFT JOIN MMM MT on
ES.ID = MT.Code AND MT.Language_Code='1' LEFT JOIN SSS as ss ON
ES.esSiteID=ss.Code and ss.Language_Code='1' LEFT JOIN VVV as VV ON
ES.ID=VV.Code and vv.Language_Code='1'

FIRST try RUNning THIS above QUERY AND SEE WHETHER YOU GET OUTPUT OR NOT or any error

In Love... With Me!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-05 : 07:38:17

Do a print or SELECT on your @SELECT + @WHERE + @ORDER to see what happens

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-05 : 07:38:42

No, you're never too old to Yak'n'Roll if you're too young to die.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-05 : 08:09:42

Try this

ALTER PROCEDURE [dbo].[xxxx]
(@LangID nvarchar(10),
@SortColumn varchar(50) = Null,
@UserID nvarchar(8) = Null)

AS
SET NOCOUNT ON

DECLARE @SELECT varchar(1000)
--DECLARE @WHERE varchar(255)
--DECLARE @ORDER varchar(50)
--
--SELECT @WHERE = ''
--SELECT @ORDER = ''

SELECT @SELECT = 'SELECT * '
SELECT @SELECT = @SELECT + ' FROM xxx as ES LEFT JOIN yyy as SE on ES.esSectorID = SE.Code AND se.Language_Code=''' + @LangID
SELECT @SELECT = @SELECT + ''' LEFT JOIN MMM MT on ES.ID = MT.Code AND MT.Language_Code=''' + @LangID
SELECT @SELECT = @SELECT + ''' LEFT JOIN SSS as ss ON ES.esSiteID=ss.Code and ss.Language_Code=''' + @LangID
SELECT @SELECT = @SELECT + ''' LEFT JOIN VVV as VV ON ES.ID=VV.Code and vv.Language_Code=''' + @LangID + ''''

-- IF A USERS BID IS PASSED IN THEN USE IT
IF @UserID IS NOT NULL
SELECT @SELECT = @SELECT +' WHERE ES.BID=''' + @UserID + ''''

-- IF A SORT WAS PASSED IN THEN USE IT
IF @SortColumn IS NOT NULL
SELECT @SELECT = @SELECT +' ORDER BY ' + @SortColumn

--SELECT @SELECT

EXEC(@SELECT )

In Love... With Me!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources