ASC Result Set with Nulls at the bottom

I have a results set that is sorted on LineNum. Not all items have a
LineNum (some are null). I want my result set to return all items in
ASC LineNum order and then list all items with NULL LineNum at the end
of the results. Any Suggestions?

Thanks.On 25 Jan 2006 12:22:36 -0800, ActiveX wrote:

>I have a results set that is sorted on LineNum. Not all items have a
>LineNum (some are null). I want my result set to return all items in
>ASC LineNum order and then list all items with NULL LineNum at the end
>of the results. Any Suggestions?

Hi ActiveX,

ORDER BY CASE WHEN LineNum IS NULL THEN 1 ELSE 0 END, LineNum

--
Hugo Kornelis, SQL Server MVP

array with sql server 2000

hello
I have created one store procedures that return a table variable

'CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
DECLARE @.tbl table(condomio_id Varchar(6),titlo varchar(5),nominativo varchar(256),stato int)
DECLARE @.colA nvarchar(50)
DECLARE @.MyCursor CURSOR
/*declare @.mycursor1 cursor*/

SET @.MyCursor = CURSOR FAST_FORWARD
FOR
Select nome_consigliere
From t_immoconsiglieri
where immobile_id=@.immobile_id
order by posizione
OPEN @.MyCursor
FETCH NEXT FROM @.MyCursor
INTO @.ColA
WHILE @.@.FETCH_STATUS = 0
BEGIN

Insert @.tbl
SELECT dbo.T_Condomini.Condomino_id,dbo.T_Condomini.titolo,dbo.T_Condomini.Nominativo,dbo.T_UniCond.StCon_id
FROM dbo.T_Condomini INNER JOIN
dbo.T_UniCond ON dbo.T_Condomini.Condomino_id = dbo.T_UniCond.Condomino_id INNER JOIN
dbo.T_Unita ON dbo.T_UniCond.Unita_id = dbo.T_Unita.Unita_id
WHERE (dbo.T_Condomini.Nominativo = @.ColA) AND (dbo.T_UniCond.Dta_fine = '21001231') AND (dbo.T_Unita.Immobile_id =@.immobile_id) and dbo.T_UniCond.StCon_id<>3
FETCH NEXT FROM @.MyCursor
INTO @.ColA
END

CLOSE @.MyCursor
DEALLOCATE @.MyCursor

select * from @.tbl

/*SET QUOTED_IDENTIFIER OFF*/
GO
,
When i call store procedure with vb6
Dim rs as new adodb.recordset
Set cmd = New ADODB.Command
Dim pm As New ADODB.Parameter
' conn.BeginTrans
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
Set pm = cmd.CreateParameter("immobile_id", adVarChar, adParamInput, 6, immobile_id)
cmd.Parameters.Append pm
cmd.CommandText = "sptcondconsiglieri"
Set rs = cmd.Execute
If Not rs.EOF Then
'
Rs is close
I dont undestand why
Tank you

Could you please provide more details?

Thanks

|||

CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
SET NOCOUNT ON

-- Original text followed

|||I guess using SET NOCOUNT ON resolved the problem. Several TSQL statements can produce results or messages. So to suppress some of the unwanted messages and read just the SELECT statement output for example you need to SET NOCOUNT ON in the SP. This will eliminate the DONE messages send to the client after the SELECT @.d = ... statement for example. See Books Online for more details on SET NOCOUNT ON effect on returning resultsets.

array with sql server 2000

hello
I have created one store procedures that return a table variable

'CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
DECLARE @.tbl table(condomio_id Varchar(6),titlo varchar(5),nominativo varchar(256),stato int)
DECLARE @.colA nvarchar(50)
DECLARE @.MyCursor CURSOR
/*declare @.mycursor1 cursor*/

SET @.MyCursor = CURSOR FAST_FORWARD
FOR
Select nome_consigliere
From t_immoconsiglieri
where immobile_id=@.immobile_id
order by posizione
OPEN @.MyCursor
FETCH NEXT FROM @.MyCursor
INTO @.ColA
WHILE @.@.FETCH_STATUS = 0
BEGIN

Insert @.tbl
SELECT dbo.T_Condomini.Condomino_id,dbo.T_Condomini.titolo,dbo.T_Condomini.Nominativo,dbo.T_UniCond.StCon_id
FROM dbo.T_Condomini INNER JOIN
dbo.T_UniCond ON dbo.T_Condomini.Condomino_id = dbo.T_UniCond.Condomino_id INNER JOIN
dbo.T_Unita ON dbo.T_UniCond.Unita_id = dbo.T_Unita.Unita_id
WHERE (dbo.T_Condomini.Nominativo = @.ColA) AND (dbo.T_UniCond.Dta_fine = '21001231') AND (dbo.T_Unita.Immobile_id =@.immobile_id) and dbo.T_UniCond.StCon_id<>3
FETCH NEXT FROM @.MyCursor
INTO @.ColA
END

CLOSE @.MyCursor
DEALLOCATE @.MyCursor

select * from @.tbl

/*SET QUOTED_IDENTIFIER OFF*/
GO
,
When i call store procedure with vb6
Dim rs as new adodb.recordset
Set cmd = New ADODB.Command
Dim pm As New ADODB.Parameter
' conn.BeginTrans
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
Set pm = cmd.CreateParameter("immobile_id", adVarChar, adParamInput, 6, immobile_id)
cmd.Parameters.Append pm
cmd.CommandText = "sptcondconsiglieri"
Set rs = cmd.Execute
If Not rs.EOF Then
'
Rs is close
I dont undestand why
Tank you

Could you please provide more details?

Thanks

|||

CREATE PROCEDURE sptcondconsiglieri @.immobile_id varchar(6)
as
SET NOCOUNT ON

-- Original text followed

|||I guess using SET NOCOUNT ON resolved the problem. Several TSQL statements can produce results or messages. So to suppress some of the unwanted messages and read just the SELECT statement output for example you need to SET NOCOUNT ON in the SP. This will eliminate the DONE messages send to the client after the SELECT @.d = ... statement for example. See Books Online for more details on SET NOCOUNT ON effect on returning resultsets.

Array / Table As return Type

Hello All,

I have a scenario in which my stored procedure has to return few
variables with their value and also the collection. Now in SQL their is
no such as array, so the best is to return the table in place of.

I am execting the stored procedure by having sql command in place.
and created the various parameters(variables) those i need the values
of and secondly wondering how should i be creating the parameter as a
table returntype.

Any help on this would be a million worth useful.

I can excerpt the code if required.

Regards
Sandesh KadamSandesh (sandesh27uk@.gmail.com) writes:

Quote:

Originally Posted by

I have a scenario in which my stored procedure has to return few
variables with their value and also the collection. Now in SQL their is
no such as array, so the best is to return the table in place of.
>
I am execting the stored procedure by having sql command in place.
and created the various parameters(variables) those i need the values
of and secondly wondering how should i be creating the parameter as a
table returntype.

You cannot pass table variables as parameters.

I have an article on my web site that discusses a couple of ways to
skin the cat: http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Are these two statements equivalent?

Both return the same records in the same order, with the same values, and I
think I'm right, but I just wanted to get some other eyeballs on this so I
can win the argument we're having here, because mine (the second one) is
more than twice as fast.
-- Uses a WHERE...IN(...) and subquery on one of the joined tables
SELECT DISTINCT ss.SampleSourceKey, dbo.ParseFilename(ss.Filename) AS
Filename
FROM SampleSource ss WITH (NOLOCK)
INNER JOIN CLMR WITH (NOLOCK)
ON clmr.SampleSourceKey = ss.SampleSourceKey
WHERE ss.SampleSourceKey IN (
SELECT clmr.SampleSourceKey
FROM CLMR WITH (NOLOCK)
WHERE CountryKey IS NULL
)
ORDER BY ss.SampleSourceKey
-- Uses a column from one of the joined tables.
SELECT DISTINCT ss.SampleSourceKey, dbo.ParseFilename(ss.Filename) AS
Filename
FROM SampleSource ss WITH (NOLOCK)
INNER JOIN CLMR WITH (NOLOCK)
ON clmr.SampleSourceKey = ss.SampleSourceKey
WHERE clmr.CountryKey IS NULL
ORDER BY ss.SampleSourceKey
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustanethe usual: if this query
SELECT clmr.SampleSourceKey
FROM CLMR WITH (NOLOCK)
WHERE CountryKey IS NULL
can ever return a null, then the first query will return nothing...|||> the usual: if this query
> SELECT clmr.SampleSourceKey
> FROM CLMR WITH (NOLOCK)
> WHERE CountryKey IS NULL
> can ever return a null, then the first query will return nothing...
Ooo I had forgotten that, but we're safe here, because clmr.SampleSourceKey
is required.
Thanks for the brainpick!
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane