sql server codes: null

Showing posts with label null. Show all posts

Friday, March 30, 2012

Help optmizing a stored proc

I have this

CREATE PROCEDURE dbo.cmsGetTaskOrdersAndFunding2
(
@.FundingDate SMALLDATETIME,
@.BillingContractID INT, -- null for all contracts
@.Filter BIT = NULL

)
AS
-- get list of taskorders with their respective fundingtotals as of
specified date
IF @.Filter IS NULL
BEGIN
SELECT TO1.TaskOrderID
FROM TaskOrder TO1
LEFT OUTER JOIN
WHERE (@.BillingContractID IS NULL OR TO1.BillingContractID =
@.BillingContractID)
END
ELSE
BEGIN
SELECT TO1.TaskOrderID,
FROM TaskOrder TO1
WHERE (@.BillingContractID IS NULL OR TO1.BillingContractID =
@.BillingContractID) AND TO1.Retired <> @.Filter
END

RETURN
GO
------
Is there a less redundant way to write this? basically @.Filter is an
optional parameter, if it isn't present, I want to return all records and if
it is present all records where Retired <> @.Filter. Any ideas? Can I wrap
the WHERE clause in an if statement? Or is there a better way?

TIA,
ChrisJust one block:

-- get list of taskorders with their respective fundingtotals as of
specified date
SELECT TO1.TaskOrderID,
FROM TaskOrder TO1
WHERE (@.BillingContractID IS NULL OR TO1.BillingContractID =
@.BillingContractID)
AND (@.Filter IS NULL OR TO1.Retired <> @.Filter)

Note this last line. If @.Filter is NULL, the entire block is ALWAYS
true so "TO1.Retired <> @.Filter" doesn't matter. If @.filter is not
null, "TO1.Retired <> @.Filter" is the part that matters.|||Sweet! Thanks man.

"figital" <mharen@.gmail.com> wrote in message
news:1141927222.299970.274010@.i39g2000cwa.googlegr oups.com...
> Just one block:
> -- get list of taskorders with their respective fundingtotals as of
> specified date
> SELECT TO1.TaskOrderID,
> FROM TaskOrder TO1
> WHERE (@.BillingContractID IS NULL OR TO1.BillingContractID =
> @.BillingContractID)
> AND (@.Filter IS NULL OR TO1.Retired <> @.Filter)
> Note this last line. If @.Filter is NULL, the entire block is ALWAYS
> true so "TO1.Retired <> @.Filter" doesn't matter. If @.filter is not
> null, "TO1.Retired <> @.Filter" is the part that matters.

Monday, March 26, 2012

help on query

Hi,
My table has a column [account type], some rows have null value, when I
query it to eliminate some account type like accout != 'A', all the rows wit
h
null account won't showup either. I would like all the accounts other than
'A' show. How can I write it? ThanksYou could:
select * from TheTable
where isnull( [account type], ''') <> 'A'
Bryce|||...
Where account type Is Null OR account type <> 'A'
"Jen" wrote:

> Hi,
> My table has a column [account type], some rows have null value, when I
> query it to eliminate some account type like accout != 'A', all the rows w
ith
> null account won't showup either. I would like all the accounts other than
> 'A' show. How can I write it? Thanks|||Try,
select * from your_table
where [account] != 'A' or [account] is null
AMB
"Jen" wrote:

> Hi,
> My table has a column [account type], some rows have null value, when I
> query it to eliminate some account type like accout != 'A', all the rows w
ith
> null account won't showup either. I would like all the accounts other than
> 'A' show. How can I write it? Thanks

Monday, March 19, 2012

Help needed with this sproc

Hi, I am trying to Implement Multi parameter...

If i give NULL it works fine but if i give '7,4' I get this error message Msg 102, Level 15, State 1, Line 18 Incorrect syntax near '17'.

This is my sproc

ALTER Procedure [dbo].[usp_GetOrdersByOrderDate]@.ClientIdnvarchar(max)=NULL,@.StartDatedatetime,@.EndDatedatetimeASDeclare @.SQLTEXT nvarchar(max)If @.ClientIdISNULLBeginSelect o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInnerJoin ClientPlan cpon o.PlanId = cp.PlanIdInnerJoin ClientUser con o.CreatedByUserId = c.UserIdWHERE--cp.ClientId = @.ClientId--AND o.OrderDateBETWEEN @.StartDateAND @.EndDateORDER BYo.OrderIdDESCENDELSEBEGIN SELECT @.SQLTEXT ='Select o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserIdWHERE cp.ClientId in (' + @.ClientId +')AND o.OrderDate BETWEEN ' +Convert(varchar,@.StartDate) +' AND ' +convert(varchar, @.EndDate) +' ORDER BYo.OrderId DESC'execute (@.SQLTEXT)END

any help will be appreciated.

Regards

Karen

Try this:

SELECT @.SQLTEXT ='Select o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserIdWHERE cp.ClientId in (' +Convert(Varchar,@.ClientId) +')AND o.OrderDate BETWEEN ' +Convert(varchar,@.StartDate) +' AND ' +convert(varchar, @.EndDate) +' ORDER BYo.OrderId DESC'Exec(@.SQLTEXT)

If you still have errors, post exactly how you are calling the proc.

|||

i am first execting the stored proc using query analyser and this is how i am calling it

usp_GetOrdersByOrderDate'7,4','12/17/2007','12/20/2007'

|||

ahh.. you need quotes around the dates too..

SELECT @.SQLTEXT ='Select o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserIdWHERE cp.ClientId in (' +Convert(Varchar,@.ClientId) +')AND o.OrderDate BETWEEN ''' +Convert(varchar,@.StartDate) +''' AND ''' +convert(varchar, @.EndDate) +''' ORDER BYo.OrderId DESC'

|||

thanks,,,

Yes that did it.

Regards

Karen

Wednesday, March 7, 2012

Help Needed - ISNULL when the field is already null..

Hello,
I just noticed today, after a long time, that if you do a
SELECT * FROM Table WHERE Field = Field
And that Field is null, it will return NOTHING at all...
Problem is, in my search stored procedures, I always use:
SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
So if the value is null, it simply avoid the check.
But now I'm screwed, for the first time I have to do this on fields that CAN
be null... and well, it returns NOTHING.
of course, WHERE Field IS NULL works like a charm, but I need this to be
working so if it's null, it skips the filter, if not, it does it.
Any help would be appreciated.Forgot to add...
the algorithm should be...
If null then don't check at all, return null and not null
but if not null, then must be it and only it...
I'm saying this, because I tried ISNULL(Field, 'foo') = ISNULL(@.Field,
'foo') and that, well, will only return rows with null values :-(
"Nicolas LeBlanc" <nicolas_leblanc@.nospamhotmail.com> a crit dans le
message de news:%23VrpzvdFFHA.3312@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that
CAN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.
>|||Try,
...
where colA = @.par1 or @.par1 is null
...
go
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"Nicolas LeBlanc" wrote:

> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that C
AN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.
>
>|||Read this article, which contains lots of info about doing this and similar
operations:
www.sommarskog.se/dyn-search.html
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Nicolas LeBlanc" <nicolas_leblanc@.nospamhotmail.com> wrote in message
news:%23VrpzvdFFHA.3312@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that
CAN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.
>|||How about :
CREATE TABLE [test] (
[col1] [int] IDENTITY (1, 1) NOT NULL ,
[col2] [datetime] NULL
) ON [PRIMARY]
GO
col1 col2
-- ---
1 2005-02-17 22:15:22.263
2 2005-02-15 22:15:24.920
3 2005-02-14 22:16:23.857
4 NULL
5 NULL
6 NULL
7 NULL
8 NULL
declare @.d datetime
set @.d = null -- try this one or the next
set @.d = '2005-02-17 22:15:22.263'
SELECT *
FROM test
WHERE
(col2 = @.d)
UNION ALL
SELECT *
FROM test
WHERE
(@.d IS NULL and col2 IS NULL)
Check execution plans to see which ones are optimized the best:
David Gugick
Imceda Software
www.imceda.com|||Based on your needs, you must use one of the two following statements:
Where (Field = @.Field)
Where @.Field is Null OR (Field = @.Field)
Where (@.Field is Null and Field is Null) OR (Field = @.Field)
Where (@.Field is Null and Field is Not Null) OR (Field = @.Field)
There is also an option in SQL-Server that will make the equality (Null =
Null) return True instead of False but I don't suggest using it; otherwise
you will have a continuous moving target.
S. L.
"Nicolas LeBlanc" <nicolas_leblanc@.nospamhotmail.com> wrote in message
news:%23VrpzvdFFHA.3312@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that
> CAN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.
>|||Personally -- if I can get away with it security-wise -- I would rather use
dynamic SQL to solve these kinds of issues...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uYbp28dFFHA.2052@.TK2MSFTNGP09.phx.gbl...
> How about :
> CREATE TABLE [test] (
> [col1] [int] IDENTITY (1, 1) NOT NULL ,
> [col2] [datetime] NULL
> ) ON [PRIMARY]
> GO
> col1 col2
> -- ---
> 1 2005-02-17 22:15:22.263
> 2 2005-02-15 22:15:24.920
> 3 2005-02-14 22:16:23.857
> 4 NULL
> 5 NULL
> 6 NULL
> 7 NULL
> 8 NULL
> declare @.d datetime
> set @.d = null -- try this one or the next
> set @.d = '2005-02-17 22:15:22.263'
> SELECT *
> FROM test
> WHERE
> (col2 = @.d)
> UNION ALL
> SELECT *
> FROM test
> WHERE
> (@.d IS NULL and col2 IS NULL)
>
>
> Check execution plans to see which ones are optimized the best:
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Seems about right. I wonder why I didn't think of that one before...
I sure won't go into dynamic sql, no way!
Merci !
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> a
crit dans le message de news:Ozl8y$dFFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Based on your needs, you must use one of the two following statements:
> Where (Field = @.Field)
> Where @.Field is Null OR (Field = @.Field)
> Where (@.Field is Null and Field is Null) OR (Field = @.Field)
> Where (@.Field is Null and Field is Not Null) OR (Field = @.Field)
> There is also an option in SQL-Server that will make the equality (Null =
> Null) return True instead of False but I don't suggest using it; otherwise
> you will have a continuous moving target.
> S. L.
> "Nicolas LeBlanc" <nicolas_leblanc@.nospamhotmail.com> wrote in message
> news:%23VrpzvdFFHA.3312@.TK2MSFTNGP15.phx.gbl...
>|||If there is only one predicate, then the simplest solution would be:
If @.Field IS NULL
SELECT ... FROM Table
Else
SELECT ... FROM Table WHERE Field = @.Field
Of course with more predicates this approach becomes unwieldly.
Gert-Jan
Nicolas LeBlanc wrote:
> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that C
AN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.

Monday, February 27, 2012

HELP ME...........

It my ddl table:
CREATE TABLE [dbo].[TTEMP_BC] (
[RecID] [int] IDENTITY (1, 1) Primary Key,
[FDATE] [smalldatetime] NULL ,
[FTIME] [smalldatetime] NULL ,
[NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
[FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
)
and my data like it:
noid fdate ftime fstatus
---
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 6:31:00 1
1 1/1/2005 1/1/2005 16:30:00 1
1 1/1/2005 1/1/2005 16:30:00 0
1 1/1/2005 1/1/2005 16:33:00 0
1 1/1/2005 1/1/2005 16:33:00 0
2 1/1/2005 1/1/2005 6:27:00 1
2 1/1/2005 1/1/2005 6:28:00 1
2 1/1/2005 1/1/2005 6:32:00 1
2 1/1/2005 1/1/2005 16:30:00 0
2 1/1/2005 1/1/2005 16:31:00 0
2 1/1/2005 1/1/2005 16:45:00 0
2 1/1/2005 1/1/2005 16:45:00 0
I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime)) of
group noid,fdate is not deleted. So its data will be;
noid fdate ftime fstatus
---
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 16:33:00 0
2 1/1/2005 1/1/2005 6:27:00 1
2 1/1/2005 1/1/2005 16:45:00 0
Can u help me? How sintax sql? Can it be solved with one statement?Hi
I think Steve Kass has already provided solution for you .
Would you mind to post a sample data when you ask for help or at least to
fix your current DDL
This is one of the many options that others provided
CREATE TABLE [dbo].[TTEMP_BC] (
[RecID] [int] IDENTITY (1, 1) Primary Key,
[NOID] [INT],
[FDATE] [smalldatetime] NULL ,
[FTIME] [smalldatetime] NULL ,
[FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:31:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:30:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:33:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:33:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:27:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:28:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:32:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:30:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:31:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:45:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:45:00',0)
SELECT * FROM
(
SELECT noid,MIN(ftime)as ftime
FROM TTEMP_BC WHERE fstatus=1
GROUP BY noid
UNION ALL
SELECT noid,MIN(ftime)as ftime
FROM TTEMP_BC WHERE fstatus=0
GROUP BY noid
) AS Der
ORDER BY noid
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:uK2KgVQkFHA.3336@.tk2msftngp13.phx.gbl...
> It my ddl table:
> CREATE TABLE [dbo].[TTEMP_BC] (
> [RecID] [int] IDENTITY (1, 1) Primary Key,
> [FDATE] [smalldatetime] NULL ,
> [FTIME] [smalldatetime] NULL ,
> [NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
> [FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
> )
> and my data like it:
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:31:00 1
> 1 1/1/2005 1/1/2005 16:30:00 1
> 1 1/1/2005 1/1/2005 16:30:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 6:28:00 1
> 2 1/1/2005 1/1/2005 6:32:00 1
> 2 1/1/2005 1/1/2005 16:30:00 0
> 2 1/1/2005 1/1/2005 16:31:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
> noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime))
of
> group noid,fdate is not deleted. So its data will be;
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 16:45:00 0
> Can u help me? How sintax sql? Can it be solved with one statement?
>
>
>|||Try,
delete t1
where
recid !=
case
when fstatus = 1 then (select min(a.recid) from t1 as a where a.fstatus = 1
and a.noid = t1.noid and a.fdate = t1.fdate and a.ftime = (select
min(b.ftime) from t1 as b where b.fstatus = 1 and b.noid = t1.noid and
b.fdate = t1.fdate))
when fstatus = 0 then (select max(a.recid) from t1 as a where a.fstatus = 0
and a.noid = t1.noid and a.fdate = t1.fdate and a.ftime = (select
max(b.ftime) from t1 as b where b.fstatus = 0 and b.noid = t1.noid and
b.fdate = t1.fdate))
end
go
AMB
"Bpk. Adi Wira Kusuma" wrote:

> It my ddl table:
> CREATE TABLE [dbo].[TTEMP_BC] (
> [RecID] [int] IDENTITY (1, 1) Primary Key,
> [FDATE] [smalldatetime] NULL ,
> [FTIME] [smalldatetime] NULL ,
> [NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
> [FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
> )
> and my data like it:
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:31:00 1
> 1 1/1/2005 1/1/2005 16:30:00 1
> 1 1/1/2005 1/1/2005 16:30:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 6:28:00 1
> 2 1/1/2005 1/1/2005 6:32:00 1
> 2 1/1/2005 1/1/2005 16:30:00 0
> 2 1/1/2005 1/1/2005 16:31:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
> noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime)) o
f
> group noid,fdate is not deleted. So its data will be;
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 16:45:00 0
> Can u help me? How sintax sql? Can it be solved with one statement?
>
>
>

Help me with SP OrderBy

I have a SP that accepts parameters, but the problem is that it is not ORDERING Data as required, It is returning ORDER BY NULL instead of order by my parameter "NAME "here is the output from SQL 2000 DB

'ELP B4 I jump out of LondonBridge

**********************************
SELECT * FROM #TempTable WHERE ID > 0 AND ID < 6 AND EmployerID = 54 AND Job_no = 40

ORDER BY NULL
****************************************


SELECT @.MYSTATEMENT = ' SELECT * FROM #TempTableWHERE
ID > '+ convert(varchar(20),@.FirstRec) +'
AND
ID < '+convert(varchar(20),@.LastRec) +'
AND EmployerID = '+ convert(varchar(20),@.EmployerID)+'
AND Job_no = '+convert(varchar(20),@.Job_no)+'
ORDER BY ' +
CASE WHEN @.WhichColumn= 'name' AND @.sortby= 'asc'
THEN 'name'
ELSE 'NULL'
END
+
CASE WHEN @.WhichColumn='name' AND @.sortby='DESC'
THEN ' desc'
ELSE ''
END
EXEC (@.myStatement)

try :


ORDER BY ' +
CASE WHEN @.WhichColumn= 'name' AND @.sortby= 'asc' THEN 'name ASC'
 WHEN @.WhichColumn = 'name' AND @.sortby = 'desc' THEN 'name desc'
ELSE 'NULL'
END

also do a print @.myStatement before you execute the statement to see how its building up.

hth|||Hi this is the output, it is still showing null

SELECT * FROM #TempTable

WHERE
ID > 0
AND
ID < 6
AND EmployerID = 54
AND Job_no = 40

ORDER BY NULL

(5 row(s) affected)|||what value are you passing to the parameter @.WhichColumn ?|||I have done a print on @.whichColumn, it is giving me the right value which is 'NAME'
So the problem must be from the case statement, but I can't figure it out

Thanks|||


declare @.MYSTATEMENT varchar(500), @.WhichColumn varchar(100), @.sortby varchar(100)
set @.sortby = 'desc'
set @.WhichColumn = 'name'
SELECT @.MYSTATEMENT = ' SELECT * FROM #TempTable WHERE ID > 6 AND ID < 10 ORDER BY ' +CASE WHEN @.WhichColumn= 'name' AND @.sortby= 'asc' THEN 'name ASC'
 WHEN @.WhichColumn = 'name' AND @.sortby = 'desc' THEN 'name desc'
ELSE 'NULL'
END
print @.mystatement

i just did this in my QA and it worked fine for me. so double check your code.

hth

help me with date

Hi i have date field. when used paratemized query from asp.net . in which
date field is null , it null the whole dynamix sql , then i decide to use
isnull function to convert null to '', i
like set @.mydate=isnull(@.mydate,'')
but this create another problem which it update date field with date
1/1/1900, but i want that field should be null instead of 1/1/1900 any one
has any idea how to do thatUse this option.
SET CONCAT_NULL_YIELDS_NULL OFF
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"amjad" wrote:

> Hi i have date field. when used paratemized query from asp.net . in which
> date field is null , it null the whole dynamix sql , then i decide to use
> isnull function to convert null to '', i
> like set @.mydate=isnull(@.mydate,'')
> but this create another problem which it update date field with date
> 1/1/1900, but i want that field should be null instead of 1/1/1900 any one
> has any idea how to do that|||or if its not in the dbend
then use DBNull.value
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Omnibuzz" wrote:
> Use this option.
> SET CONCAT_NULL_YIELDS_NULL OFF
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "amjad" wrote:
>|||amjad wrote:
> Hi i have date field. when used paratemized query from asp.net . in which
> date field is null , it null the whole dynamix sql , then i decide to use
> isnull function to convert null to '', i
> like set @.mydate=isnull(@.mydate,'')
> but this create another problem which it update date field with date
> 1/1/1900, but i want that field should be null instead of 1/1/1900 any one
> has any idea how to do that
Your dynamic SQL needs to set the date field equal to NULL instead of
''. Post the code that builds your dynamic query.