please help me brothers!!!! with 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

Transact-SQL (2000)

please help me brothers!!!! with stored procedure

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2006-04-08 : 01:02:53

Hi All please can anyone complete my this stored procedure i am really frustrated with MS SQL Server i don't know what si going wrong wiht dateadd function and i also want to use case condition inside my procedure and i am not professional with sql TSQL so please can anyone complete this query please this is not wokring at all and alos please someone try to complete this and this on bloody not wokring at all and also tell me how to check with passing the value when i chekc with sql i am really much need to complete this as soon as possible please someone complete this for my searching page please please :((.

CREATE PROCEDURE Search_Positions

@experience nvarchar(2500) = NULL,

@fromdate datetime = NULL,

@searchkeyword nvarchar(2500) = NULL,

@country nvarchar(255) = NULL,

@state nvarchar(255) = NULL,

@city nvarchar(255) = NULL,

@zipcode nvarchar(2500) = NULL,

@prefsortby varchar(55) = NULL,

@debug bit = 0 AS

DECLARE @sql nvarchar(4000),

@paramlist nvarchar(4000)

SELECT @sql = 'SELECT tp.fld_postpositionid, tp.fld_postcompname, tp.fld_postpostitle,

CONVERT(varchar,fld_postdateofentry,107) As postdate,

tp.fld_city,tlpstate.type_description As State,tlpcountry.type_description As Country

FROM table1 tp

JOIN [table2] tlpcountry ON (tp.fld_country = tlpcountry.lookup_id)

JOIN [table3] tlpstate ON (tp.fld_state = tlpstate.lookup_id)

WHERE 1 = 1'

IF @experience IS NOT NULL

SELECT @sql = @sql + ' AND tp.fld_postyearsofskillexpprefer = @experience'

IF @fromdate IS NOT NULL

SELECT @sql = @sql + ' AND tp.fld_postdateofentry >= Dateadd(DD,@xfromdate,GETDATE())'

IF @searchkeyword IS NOT NULL

SELECT @sql = @sql + ' AND @xsearchkeyword'

IF @country IS NOT NULL

SELECT @sql = @sql + ' AND tp.fld_country >= @xcountry'

IF @state IS NOT NULL

SELECT @sql = @sql + ' AND tp.fld_state <= @xstate'

IF @city IS NOT NULL

SELECT @sql = @sql + ' AND tp.fld_city = @xcity'

IF @zipcode IS NOT NULL

SELECT @sql = @sql + ' AND tp.fld_zip IN(@zipcode)'

IF @prefsortby IS NOT NULL

SELECT @sql = @sql + ' AND @prefsortby'

--SELECT @sql = @sql + ' ORDER BY o.OrderID'

IF @debug = 1

PRINT @sql

SELECT @paramlist = '@xexperience nvarchar(2500),

@xfromdate datetime,

@xsearchkeyword nvarchar(2500),

@xcountry nvarchar(255),

@xstate nvarchar(255),

@xcity nvarchar(255),

@xzipcode nvarchar(2500),

@xprefsortby varchar(55)'

EXEC sp_executesql @sql, @paramlist,

@experience, @fromdate, @searchkeyword, @country,
@state, @city, @zipcode, @prefsortby

Kristen
Test

22859 Posts

Posted - 2006-04-08 : 01:13:22

Why do you need to use dynamic SQL in your SProc?

Kristen

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-08 : 01:54:27

Instead of using series if else you can AND and OR..

Somthing like this

Select Column_Name.... From TableName....
Where
(@experience IS NULL or tp.fld_postyearsofskillexpprefer = @experience) And
(@fromdate IS NULL or tp.fld_postdateofentry >= Dateadd(DD,@xfromdate,GETDATE()))
............

Hope this helps

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-10 : 03:38:03

Also refer http://www.sqlteam.com/item.asp?ItemID=2077

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources