I am sure table valued function will allow parameters but why cant we pass dbname as parameter and execute it from any where in same server

Can I make a view as table valued function so that I can run the view in any db by passing the database name as parameter. something like this....

SET ANSI_NULLS...

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...

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...

hey i didnt follow tht..

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

@begindate datetime

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...

tim ..if i had sql 2005, then it wud work for max length but am using sql 2000.

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

Vasc...

yeah my length is more than 4k and so iwant to do some type break and concatinate.

If I break my dynamic sql and then concatinate it, like

@sqldcmd1 + @sqlcmd2, how am i going to use

exec sp_executesql @SQLCmd1,N'@Col datetime',@Col

Ray M (12/13/2007)

---

Your not constructing your dynamic sql correctly.

if you want to pass parameters into dynamic sql, then you MUST use sp_executesql. the EXEC function does not except parameters.

If...

yeah my Fromdate is a varchar as i told you and now i tried doing cast(fromdate as datetime) i get hte result set ok but with an error message

Msg 241,...

As I made this proc dynamic by passing dbname as parameter so that i can run this proc in any db, can i also do the same for views.

I want...

THank yuou very much Ray

I cud do that but am getting another error

Msg 241, Level 16, State 1, Line 6

Syntax error converting datetime from character string.

Could you explain this for me in detail, why do you use parameters double time there. I tried this it gives syntax error

exec sp_executesql(@SQLCmd ,

'@beginDate datetime,

@EndDate datetime,

@emp...