Dynamic SQL with Order By. - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Dynamic SQL with Order By.

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2009-10-20 : 23:32:50

Hi.
I've made a sorting that uses dynamic sql.
I was wondering if what i did is correct, since you execute this by the column names in an application.

Also i've tryied to make a CASE statement that will select what order to execute but i get an error of "Error converting data type nvarchar to bigint." an.name =nvarchar(100) and all the other id's is bigint.

Dynamic SQL for opinion:

USE [Megatest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




	ALTER PROCEDURE [dbo].[sp_selectdoctorsdynamicsql]
	
(
@setdoctorid bigint =null,
@setownerid bigint=null,
--@orderedx int =Null
@OrderByC nvarchar(100)

)
		

	AS
	 BEGIN
		set nocount off
	
	DECLARE @sql nvarchar(4000);


-- set @orderer = @orderedx;


SET @sql =
     N'select an.id,an.name,doctors.id,an.doctorid,owners.id,an.ownerid
from 
 Animals as An  INNER JOIN
            Doctors ON An.doctorid = Doctors.id INNER JOIN
            Owners ON An.ownerid = Owners.id
WHERE 1 = 1 ';
 SELECT @sql = @sql +  'And doctors.id = @setdoctorid ';
SELECT @sql = @sql + 'and owners.id = @setownerid order by ' + @OrderByC;


EXECUTE sp_executesql  @sql, N'@setdoctorid bigint, @setownerid bigint, @OrderByC nvarchar(100) ',
                   @setdoctorid, @setownerid,@OrderByC

	END

Case procedure test with errors :

 USE [Megatest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




	ALTER PROCEDURE [dbo].[sp_selecttest2]
	
(
@setdoctorid bigint =null,
@setownerid bigint=null,
@OrderByC nvarchar(100)

)
		

	AS
	 BEGIN
		set nocount off
	
	


select an.id,an.name,doctors.id,an.doctorid,owners.id,an.ownerid
from 
 Animals as An  INNER JOIN
            Doctors ON An.doctorid = Doctors.id INNER JOIN
            Owners ON An.ownerid = Owners.id
WHERE 1 = 1 
  And doctors.id = @setdoctorid 
and owners.id = @setownerid order by CASE WHEN @OrderByC=an.name THEN CONVERT(char(50), an.name) + CONVERT(bigint, an.id)
       WHEN @OrderByC=an.id THEN CONVERT(bigint, doctors.id)
       WHEN @OrderByC=owners.id THEN owners.id end 

end

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-21 : 00:32:11

quote:

Case procedure test with errors :
[code] USE [Megatest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_selecttest2]

(
@setdoctorid bigint =null,
@setownerid bigint=null,
@OrderByC nvarchar(100)

)

AS
BEGIN
set nocount off

select an.id,an.name,doctors.id,an.doctorid,owners.id,an.ownerid
from
Animals as An INNER JOIN
Doctors ON An.doctorid = Doctors.id INNER JOIN
Owners ON An.ownerid = Owners.id
WHERE 1 = 1
And doctors.id = @setdoctorid
and owners.id = @setownerid order by CASE WHEN @OrderByC=an.name THEN CONVERT(char(50), an.name) + CONVERT(bigint, an.id)
WHEN @OrderByC=an.id THEN CONVERT(bigint, doctors.id)
WHEN @OrderByC=owners.id THEN owners.id end

end

What are you trying to do here?.if you just want to do a string concatenation,convert the id to varchar instead of bigint. Again, all the predicates in the CASE statement should be in the compatible data type.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2009-10-21 : 00:56:46

sorry ignore char(50), it's nvarchar(50).
Also "an.id" is of type bigint. Should it work with varchar?
Will be back in some hours.Gota sleep :)

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-21 : 01:21:45

And what will be the expected parameter values for @OrderByC ? As per your query it is compared against the actual column data.Does it make sense? Is the user supposed to supply the actual values of aniamal name,animal id or owner id to get it sorted on that order?. Should'nt it be some thing like below?

order by
CASE
WHEN @OrderByC='name' THEN an.name + CONVERT(varchar(50), an.id)
WHEN @OrderByC='doctorid' THEN CONVERT(varchar(50), doctors.id)
WHEN @OrderByC='ownerid' THEN CONVERT(varchar(50),owners.id)
END

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2009-10-21 : 12:22:35

Hi.
Yes something like this.I'm only trouble because i don't know if the 'name' THEN an.name + CONVERT(varchar(50), an.id) will work on dynamic sql. (will built it tonight). Will let you know,thanks.

P.S. Also you or someone on any thought on the first bunch of code (dynamic sql code).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-21 : 12:27:29

do you really need dynamic sql here?

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2009-10-21 : 19:41:01

Hi.
Work ok now, thanks, will try it on dynamic SQL later on to see how it goes.
visakh16, i'm using dynamic because this is the most critical page i'm developing and i'm trying to avoid SQL injections (with sp_executesql), but i'm not very familiar with sql development, so if i'm doing something wrong please let me know.
Thanks.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2009-10-21 : 20:43:27

Ok, now my problem is ASC and DESC..
I use the below but it does not seem to understand the commands.(always sows asc (default))

CASE WHEN @OrderByC=1 THEN  Convert(nvarchar(100),an.name)+' ASC'  + CONVERT(varchar(50),an.id) 
WHEN @OrderByC=2 THEN  Convert(nvarchar(100),an.name) + ' desc ' + CONVERT(varchar(50),an.id)

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2009-10-23 : 21:40:28

been struggling a little bit with this one.

order by
CASE
WHEN @OrderByC='name' THEN an.name + CONVERT(varchar(50), an.id)
WHEN @OrderByC='doctorid' THEN CONVERT(varchar(50), doctors.id)
WHEN @OrderByC='ownerid' THEN CONVERT(varchar(50),owners.id)
END

is there a way to include an ASC or DESC to the an.name variable?
If i do an.name + ASC will probably execute as an.name, ASC, an.id
so is there a way to execute as an.name ASC, an.id ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-24 : 04:57:26

quote:
Originally posted by sapator

been struggling a little bit with this one.

order by
CASE
WHEN @OrderByC='name' THEN an.name + CONVERT(varchar(50), an.id)
WHEN @OrderByC='doctorid' THEN CONVERT(varchar(50), doctors.id)
WHEN @OrderByC='ownerid' THEN CONVERT(varchar(50),owners.id)
END

is there a way to include an ASC or DESC to the an.name variable?
If i do an.name + ASC will probably execute as an.name, ASC, an.id
so is there a way to execute as an.name ASC, an.id ?

you need to use different cases for each column in order by. case expression should always return single column value

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-24 : 05:03:57

also you cant change the sort direction using a case

like case condn1 then 'ASC' else 'DESC' end unless you use dynamic sql

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2009-10-25 : 00:34:49

I did this:

.........
@OrderByC int=null

)
		

	AS
	 BEGIN
		set nocount off
	
	DECLARE @sql nvarchar(4000);





SET @sql =
     N'select doctors.name,doctors.phone,doctors.lastname
from  Doctors 
WHERE 1 = 1 ';
 SELECT @sql = @sql +  'And doctors.id in (1,2,3,4,5,6) order by
CASE WHEN CONVERT(varchar(50), @orderbyc)=1 THEN doctors.name
END ASC,
CASE WHEN @orderbyc=1 THEN doctors.lastname
END ASC,
CASE WHEN @orderbyc=2 THEN doctors.name
END ASC,
CASE WHEN @orderbyc=2 THEN doctors.lastname
END DESC,
CASE WHEN @orderbyc=3 THEN doctors.name
END DESC,
CASE WHEN @orderbyc=3 THEN doctors.lastname
END ASC,
CASE WHEN @orderbyc=4 THEN doctors.name
END DESC,
CASE WHEN @orderbyc=4 THEN doctors.lastname
END DESC,
phone ';

EXECUTE sp_executesql  @sql, N'@OrderByC bigint ',
                   @OrderByC

	END

I stripped the other variables temporarily so i can get a clean list of names...Don't know if it's a good try please comment.

Also i seed not to be able to pass a variable in "And doctors.id in (1,2,3,4,5,6)" if i pass a @testvar like this: And doctors.id in (@testvar)" when i try '1-2-3-4-5' i get only one line, when i try '(1,2,3,4)' i get Error converting data type varchar to bigint.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2009-10-25 : 21:15:34

currently i'm trying this with no success (no data shown)



	create PROCEDURE [dbo].[sp_sel2]	
	AS
	 BEGIN
		set nocount off
	
	DECLARE @IDIN VARCHAR(50)
set @IDIN ='1,2,3'
set @IDIN = replace(@IDIN, '''', '''''' )

   select doctors.name,doctors.phone,doctors.lastname
from  Doctors   
WHERE  CONVERT(varchar(50), doctors.id) in (' + @IDIN + ')
if @@rowcount <> ( LEN( @IDIN + ',' ) - LEN( REPLACE( @IDIN + ',', ',', '' ) ))
begin


                  print cast( ( LEN( @IDIN + ',' ) - LEN( REPLACE( @IDIN + ',', ',', '' ) ) ) as varchar(50) )
end
end

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2009-10-26 : 13:21:53

Guys ,any ideas?
I'm kinda stuck with the "in" statement and i want to avoid using the select inside my vb code. Is there a way? Or even a function that will do a replace?Something?
Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources