Dynamic Proc

Question

Dynamic Proc

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Vasc SSCarpal Tunnel Points: 4623 More actions · Answer 1

If you really want to use the format with parameters than you CAN T put your command into a variable and have more than 4k chras since for SP_EXECUTESQL complex expressions are not allowed as variables so your stuck with this format wich might be K for what you need:(the SQL command has more than 4k chars)

exec SP_EXECUTESQL N'

SET NOCOUNT ON

SELECT ''1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'',@a UNION ALL

SELECT ''1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'',@a

',N'@a NVARCHAR(10)','xxx'

Kindest Regards,

Vasc

Jack Corbett SSC Guru Points: 184388 More actions · Answer 2

Since it is dynamic sql why not do this:

ALTER Procedure dbo.showjobs

@dbname varchar(20),

@beginDate datetime,

@EndDate datetime,

@emp varchar(4)

as

begin

set nocount on

Declare @use varchar(100)

Declare @select varchar(3000)

Declare @where varchar(2000)

declare @SQLCmd varchar(5000)

Set @use = 'use ' + @DBName + ';'

set @beginDate = (Select changedate(@beginDate))

set @EndDate = (Select changedate(@EndDate))

Set @select = 'Select empid, jobid, jobname from emp'

Set @where = 'Where jobDate Between ' + QuoteName(@beginDate, '''') + ' and ' + QuoteName(@EndDate, '''')

if lower(@emp) <> 'all'

Begin

Set @Where = @Where + ' and emp = ' + QUoteName(@emp, '''')

End

Set @SqlCMd = @use + ' ' + @select + ' ' + @where

Select @SQLCmd

exec (@SQLCmd)

end

The QuoteName function takes care of putting the quotes around your character data and now you have no parameters being passed to the dynamic sql it is all handle in this stored procedure.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

jeff ...there is no other go, i want ot use only dynamic sql

Heh... you must not have looked because I gave you dynamic SQL. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Mike Levan SSChampion Points: 14201 More actions · Answer 4

still working on this with some errors...

syntax error converting datetime from character string

when i pass '07/01/2005' for the parameter @begindate i get the value 'Jul 1 2005 12:00AM'

am cheking this by printing the @sqlCmd in my proc.

is this the reason for my syntax error above?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

What is the datatype of the JobDate column and what does the ChangeDate function return as a datatype?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Mike Levan SSChampion Points: 14201 More actions · Answer 6

Mike Levan

SSChampion

Points: 14201

December 18, 2007 at 11:26 am

#761166

@begindate datetime

Vasc SSCarpal Tunnel Points: 4623 More actions · Answer 7

Mike Levan (12/18/2007)
@begindate datetime

problem is when you pass param you pass a string and the SQL engine does the conversion for you. If you want to work all the time you ll pass the param as datetime variable not string or string in this format 'YYYYMMDD' otherwise you need to pass it as string and inside convert it to datetime based on a format that you specify

you can use SET DATEFORMAT ...

Kindest Regards,

Vasc

Mike Levan SSChampion Points: 14201 More actions · Answer 8

hey i didnt follow tht..

i am declaring my paramter as datetime and passing the parameter value as '07/01/2005'

Vasc SSCarpal Tunnel Points: 4623 More actions · Answer 9

Mike Levan (12/18/2007)
hey i didnt follow tht..
i am declaring my paramter as datetime and passing the parameter value as '07/01/2005'

you should read some articles about Manipulating And Using DateTime Data in SQL server to understand the issue.

'07/01/2005' is a varchar data type NOT datetime. Now the engine must be told that your format is 'MM/DD/YYYY' otherwise he will try to convert this string based on your settings to a datetime value which might fail based on your settings.

Kindest Regards,

Vasc

Mike Levan SSChampion Points: 14201 More actions · Answer 10

when i tried doing this

select startDate from tblemp where startDate between '1/1/2006' and '12/31/2006'

i dont get that date range, why?

startdate is varchar(100), I Cant change that in the table, its there by default

Vasc SSCarpal Tunnel Points: 4623 More actions · Answer 11

Mike Levan (12/18/2007)
when i tried doing this
select startDate from tblemp where startDate between '1/1/2006' and '12/31/2006'
i dont get that date range, why?
startdate is varchar(100), I Cant change that in the table, its there by default

SET DATEFORMAT MDY --this inform the SQL engine to expect first position the month, secodn the day, and last the year

select startDate from tblemp where startDate between '1/1/2006' and '12/31/2006'

Kindest Regards,

Vasc

Jack Corbett SSC Guru Points: 184388 More actions · Answer 12

Mike Levan (12/18/2007)
when i tried doing this
select startDate from tblemp where startDate between '1/1/2006' and '12/31/2006'
i dont get that date range, why?
startdate is varchar(100), I Cant change that in the table, its there by default

You are not getting the range because the query is using string for the range. Basically, the first "date" you are passing to the query '1/1/2006' is AFTER '12/31/2006' because the code for the 3rd character "1" is > the third character "3".

See in data type preference SQL Server will convert the string '1/1/2006' to a date when comparing to a datetime data type, but when both are strings it compares using string and '1/1/2006' is after '12/31/2006' when doing string comparison. The between statement is converted behind the scenes to >= and = '1/1/2006' and <= '12/31/2006'.

If you want the query to work correctly you would need to Convert(StartDate, datetime) between '1/1/2006' and '12/31/2006', but doing this will cause the query to ignore any index on StartDate as it need to Convert each row in the table. Another option is to add a computed column on the table RealStartDate that is a datetime or smalldatetime type and convert StartDate to datetime on that column and then index the computed column. A third option is to force the data to be in YYYYMMDD format in the database then you could use between.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

Mike Levan (12/18/2007)
when i tried doing this
select startDate from tblemp where startDate between '1/1/2006' and '12/31/2006'
i dont get that date range, why?
startdate is varchar(100), I Cant change that in the table, its there by default

And THAT, Ladies and Gentlemen, is why I wrote the following... would have saved a lot of time if we knew THAT...

http://qa.sqlservercentral.com/articles/Best+Practices/61537/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Mike Levan SSChampion Points: 14201 More actions · Answer 14

want to know best way to convert a varchar field like 01/07/2006 into datetime

so that i can compare dates.

I tried many ways and got the result set with an error message

Msg 241, Level 16, State 1, Line 1

Syntax error converting datetime from character string.

Jack Corbett SSC Guru Points: 184388 More actions · Answer 15

PLease post your code. Also if you are running this against a column in a table there is an invalid date. Look in BOL for valid dates for the DateTime and SmallDateTime data types.

Run this to find the invalid date:

Select {date} from {table} where ISDATE({date}) = 0

The IsDate returns 1 if it is a valid date and 0 if not a valid date.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question