help for T-SQL code generator for DataMart

I replicated a table from DW to DM

but i filtered the table by month.

now i have several tables with the same schema on the datamart

the table has five keys. what i want to do is to write a

sqlwizard code that will automatically write an update statement from the replicated

table. What the wizard will do is read the fields of the DM table then identify the keys and

generate the source code for update.

lets name the proc sqlwiz

exec sqlwiz (DMtable1,dwtable1)

the sp should return the desired update statement like this

update dmtable1 set DM.nonkeyfield1= dw.nonkeyfield1,

DM.nonkeyfield2= dw.nonkeyfield2,

DM.nonkeyfield3= dw.nonkeyfield3

from dwtable1 dw where

dm.keyfield1=dw.keyfield1 and

dm.keyfield2=dw.keyfield2

pls use any of the northwind table with composite pk.

my DM is sql2k5.

the sp can also be used for generating update codes for vb.net

thanks,

joey

1. You will need to use Dynamic SQL
2. Make use of INFORMATION_SCHEMA.COLUMNS

Help - Simple Question

I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
DECLARE @.rc INT;
SELECT ... FROM table1 WHERE ... ;
SET @.rc = @.@.ROWCOUNT;
IF @.rc > 0
SELECT ... FROM table2 WHERE ... ;

> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
Ugh. I am sure what you are doing could be done with a simple join, rather
than a nested cursor of some kind, which should be avoided at all costs (in
most situations). If you can do a better job describing exactly what you
want to do (see http://www.aspfaq.com/5006 )... I am sure someone can help
you with a much more efficient, set-based approach.
A|||Without seeing your DDL - and the query - it's hard to say. Perhaps what
you want is a derived table or Common Table Expression.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.|||Code
BOL's example
USE pubs
DECLARE @.RowCount int
EXEC sp_executesql
N'SELECT @.RowCount = COUNT(*) FROM authors',
N'@.RowCount int OUTPUT',
@.RowCount OUTPUT
RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
>
> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
>|||Thank, you this is enough to get me going again!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23d26T0yVGHA.5580@.TK2MSFTNGP11.phx.gbl...
> DECLARE @.rc INT;
> SELECT ... FROM table1 WHERE ... ;
> SET @.rc = @.@.ROWCOUNT;
> IF @.rc > 0
> SELECT ... FROM table2 WHERE ... ;
>
> Ugh. I am sure what you are doing could be done with a simple join,
> rather than a nested cursor of some kind, which should be avoided at all
> costs (in most situations). If you can do a better job describing exactly
> what you want to do (see http://www.aspfaq.com/5006 )... I am sure someone
> can help you with a much more efficient, set-based approach.
> A
>|||Uri, wy do you resort to dynamic SQL here?

> Code
> BOL's example
> USE pubs
> DECLARE @.RowCount int
> EXEC sp_executesql
> N'SELECT @.RowCount = COUNT(*) FROM authors',
> N'@.RowCount int OUTPUT',
> @.RowCount OUTPUT
> RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)|||u need to give more information on what is it that you want to select.
for a newbie try to unlearn whatever you have learnt about cursors :)
you need to look at the result set as a whole when you are processing and
not a single record ar a time.|||No attack :-)))) Yep , I was reading some article in the BOL and just put
this examle out .
It is probably time to go home after very long work day
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OsSRF3yVGHA.4340@.TK2MSFTNGP10.phx.gbl...
> Uri, wy do you resort to dynamic SQL here?
>
>|||I juz think Uri is trying to make a point.. And what would that be'|||Hello,
When you state:
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.
are you referring specifically to cursors or do you extend this thought to
everything
encompassed in a SELECT statement?I think your making a point but I want to
know
exactly what it is:)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4873AB17-3EF3-42B0-A389-C9927E899EC0@.microsoft.com...
> u need to give more information on what is it that you want to select.
> for a newbie try to unlearn whatever you have learnt about cursors :)
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.

Help - Simple Question

I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
DECLARE @.rc INT;
SELECT ... FROM table1 WHERE ... ;
SET @.rc = @.@.ROWCOUNT;
IF @.rc > 0
SELECT ... FROM table2 WHERE ... ;

> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
Ugh. I am sure what you are doing could be done with a simple join, rather
than a nested cursor of some kind, which should be avoided at all costs (in
most situations). If you can do a better job describing exactly what you
want to do (see http://www.aspfaq.com/5006 )... I am sure someone can help
you with a much more efficient, set-based approach.
A|||Without seeing your DDL - and the query - it's hard to say. Perhaps what
you want is a derived table or Common Table Expression.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.|||Code
BOL's example
USE pubs
DECLARE @.RowCount int
EXEC sp_executesql
N'SELECT @.RowCount = COUNT(*) FROM authors',
N'@.RowCount int OUTPUT',
@.RowCount OUTPUT
RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
>
> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
>|||Thank, you this is enough to get me going again!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23d26T0yVGHA.5580@.TK2MSFTNGP11.phx.gbl...
> DECLARE @.rc INT;
> SELECT ... FROM table1 WHERE ... ;
> SET @.rc = @.@.ROWCOUNT;
> IF @.rc > 0
> SELECT ... FROM table2 WHERE ... ;
>
> Ugh. I am sure what you are doing could be done with a simple join,
> rather than a nested cursor of some kind, which should be avoided at all
> costs (in most situations). If you can do a better job describing exactly
> what you want to do (see http://www.aspfaq.com/5006 )... I am sure someone
> can help you with a much more efficient, set-based approach.
> A
>|||Uri, wy do you resort to dynamic SQL here?

> Code
> BOL's example
> USE pubs
> DECLARE @.RowCount int
> EXEC sp_executesql
> N'SELECT @.RowCount = COUNT(*) FROM authors',
> N'@.RowCount int OUTPUT',
> @.RowCount OUTPUT
> RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)|||u need to give more information on what is it that you want to select.
for a newbie try to unlearn whatever you have learnt about cursors
you need to look at the result set as a whole when you are processing and
not a single record ar a time.|||No attack :-)))) Yep , I was reading some article in the BOL and just put
this examle out .
It is probably time to go home after very long work day
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OsSRF3yVGHA.4340@.TK2MSFTNGP10.phx.gbl...
> Uri, wy do you resort to dynamic SQL here?
>
>|||I juz think Uri is trying to make a point.. And what would that be'|||Hello,
When you state:
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.
are you referring specifically to cursors or do you extend this thought to
everything
encompassed in a SELECT statement?I think your making a point but I want to
know
exactly what it is
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4873AB17-3EF3-42B0-A389-C9927E899EC0@.microsoft.com...
> u need to give more information on what is it that you want to select.
> for a newbie try to unlearn whatever you have learnt about cursors
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.

Help - Simple Question

I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.
>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
DECLARE @.rc INT;
SELECT ... FROM table1 WHERE ... ;
SET @.rc = @.@.ROWCOUNT;
IF @.rc > 0
SELECT ... FROM table2 WHERE ... ;

> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
Ugh. I am sure what you are doing could be done with a simple join, rather
than a nested cursor of some kind, which should be avoided at all costs (in
most situations). If you can do a better job describing exactly what you
want to do (see http://www.aspfaq.com/5006 )... I am sure someone can help
you with a much more efficient, set-based approach.
A
|||Without seeing your DDL - and the query - it's hard to say. Perhaps what
you want is a derived table or Common Table Expression.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.
|||Code
BOL's example
USE pubs
DECLARE @.RowCount int
EXEC sp_executesql
N'SELECT @.RowCount = COUNT(*) FROM authors',
N'@.RowCount int OUTPUT',
@.RowCount OUTPUT
RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
>
> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
>
|||Thank, you this is enough to get me going again!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23d26T0yVGHA.5580@.TK2MSFTNGP11.phx.gbl...
> DECLARE @.rc INT;
> SELECT ... FROM table1 WHERE ... ;
> SET @.rc = @.@.ROWCOUNT;
> IF @.rc > 0
> SELECT ... FROM table2 WHERE ... ;
>
> Ugh. I am sure what you are doing could be done with a simple join,
> rather than a nested cursor of some kind, which should be avoided at all
> costs (in most situations). If you can do a better job describing exactly
> what you want to do (see http://www.aspfaq.com/5006 )... I am sure someone
> can help you with a much more efficient, set-based approach.
> A
>
|||Uri, wy do you resort to dynamic SQL here?

> Code
> BOL's example
> USE pubs
> DECLARE @.RowCount int
> EXEC sp_executesql
> N'SELECT @.RowCount = COUNT(*) FROM authors',
> N'@.RowCount int OUTPUT',
> @.RowCount OUTPUT
> RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)
|||u need to give more information on what is it that you want to select.
for a newbie try to unlearn whatever you have learnt about cursors
you need to look at the result set as a whole when you are processing and
not a single record ar a time.
|||No attack :-)))) Yep , I was reading some article in the BOL and just put
this examle out .
It is probably time to go home after very long work day
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OsSRF3yVGHA.4340@.TK2MSFTNGP10.phx.gbl...
> Uri, wy do you resort to dynamic SQL here?
>
>
|||I juz think Uri is trying to make a point.. And what would that be?
|||Hello,
When you state:
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.
are you referring specifically to cursors or do you extend this thought to
everything
encompassed in a SELECT statement?I think your making a point but I want to
know
exactly what it is
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4873AB17-3EF3-42B0-A389-C9927E899EC0@.microsoft.com...
> u need to give more information on what is it that you want to select.
> for a newbie try to unlearn whatever you have learnt about cursors
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.

Help - Simple Question

I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
DECLARE @.rc INT;
SELECT ... FROM table1 WHERE ... ;
SET @.rc = @.@.ROWCOUNT;
IF @.rc > 0
SELECT ... FROM table2 WHERE ... ;
> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
Ugh. I am sure what you are doing could be done with a simple join, rather
than a nested cursor of some kind, which should be avoided at all costs (in
most situations). If you can do a better job describing exactly what you
want to do (see http://www.aspfaq.com/5006 )... I am sure someone can help
you with a much more efficient, set-based approach.
A|||Without seeing your DDL - and the query - it's hard to say. Perhaps what
you want is a derived table or Common Table Expression.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.|||Thank, you this is enough to get me going again!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23d26T0yVGHA.5580@.TK2MSFTNGP11.phx.gbl...
> >I am running a T-SQL query and if there are one or more records returned
> >by it I need to run another query. How does a newbie like me determine
> >this or how do I get the value of the COUNT function into a variable I
> >can use elsewhere in my sproc?
> DECLARE @.rc INT;
> SELECT ... FROM table1 WHERE ... ;
> SET @.rc = @.@.ROWCOUNT;
> IF @.rc > 0
> SELECT ... FROM table2 WHERE ... ;
>> After I see that the first query has records I am going to use values
>> from it to run the second query and I assume I am going to use a cursor
>> to accomplish this. Thank you.
> Ugh. I am sure what you are doing could be done with a simple join,
> rather than a nested cursor of some kind, which should be avoided at all
> costs (in most situations). If you can do a better job describing exactly
> what you want to do (see http://www.aspfaq.com/5006 )... I am sure someone
> can help you with a much more efficient, set-based approach.
> A
>|||Uri, wy do you resort to dynamic SQL here?
> Code
> BOL's example
> USE pubs
> DECLARE @.RowCount int
> EXEC sp_executesql
> N'SELECT @.RowCount = COUNT(*) FROM authors',
> N'@.RowCount int OUTPUT',
> @.RowCount OUTPUT
> RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)|||u need to give more information on what is it that you want to select.
for a newbie try to unlearn whatever you have learnt about cursors :)
you need to look at the result set as a whole when you are processing and
not a single record ar a time.|||No attack :-)))) Yep , I was reading some article in the BOL and just put
this examle out .
It is probably time to go home after very long work day
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OsSRF3yVGHA.4340@.TK2MSFTNGP10.phx.gbl...
> Uri, wy do you resort to dynamic SQL here?
>
>> Code
>> BOL's example
>> USE pubs
>> DECLARE @.RowCount int
>> EXEC sp_executesql
>> N'SELECT @.RowCount = COUNT(*) FROM authors',
>> N'@.RowCount int OUTPUT',
>> @.RowCount OUTPUT
>> RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)
>|||I juz think Uri is trying to make a point.. And what would that be'|||Code
BOL's example
USE pubs
DECLARE @.RowCount int
EXEC sp_executesql
N'SELECT @.RowCount = COUNT(*) FROM authors',
N'@.RowCount int OUTPUT',
@.RowCount OUTPUT
RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
>
> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
>|||Hello,
When you state:
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.
are you referring specifically to cursors or do you extend this thought to
everything
encompassed in a SELECT statement?I think your making a point but I want to
know
exactly what it is:)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4873AB17-3EF3-42B0-A389-C9927E899EC0@.microsoft.com...
> u need to give more information on what is it that you want to select.
> for a newbie try to unlearn whatever you have learnt about cursors :)
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.

Help - Just using the time of DateTime or SmallDateTime

I need to store the time in a table ("10:00:00 AM") and then compare just
the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
this is simple as heck but because I am a newbie I am stumbling. Can
someone provide me with a sample of this? Thank you.What datatype is the column in the table of?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Code Boy" <CodeBoy@.microsoft.com> wrote in message news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.ph
x.gbl...
>I need to store the time in a table ("10:00:00 AM") and then compare just
> the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
> this is simple as heck but because I am a newbie I am stumbling. Can
> someone provide me with a sample of this? Thank you.
>|||That is up to me (so whatever you tell me). I just want to end up selecting
(with T-SQL) any row where a column has a time only greater then the time
portion of the current time stamp. Thank you.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMb8aRNVGHA.4740@.TK2MSFTNGP14.phx.gbl...
> What datatype is the column in the table of?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Code Boy" <CodeBoy@.microsoft.com> wrote in message
> news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.phx.gbl...|||code
select convert(varchar(8),current_timestamp,108
) , it will give you
time.
You have to compare it with time in your table.
Look at convert function for more details in BOL.
Regards
Amish Shah.|||When I do this the compare to the time in my table does not select anyting.
It is as if now that they are varchars the >= operator does not work
properly. I will look in BOL for more info. Thank you.
"amish" <shahamishm@.gmail.com> wrote in message
news:1143820529.897848.99710@.g10g2000cwb.googlegroups.com...
> code
> select convert(varchar(8),current_timestamp,108
) , it will give you
> time.
> You have to compare it with time in your table.
> Look at convert function for more details in BOL.
> Regards
> Amish Shah.
>|||>I need to store the time in a table ("10:00:00 AM") and then compare just
>the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL.
Okay, so when you insert the data into SQL Server, make sure the date is not
included (or manually force it to be 1900-01-01 on insert).
Now, you can compare to CURRENT_TIMESTAMP - DATEDIFF(DAY, 0, GETDATE()). An
example:
CREATE TABLE #foo
(
FooID INT,
NextBar SMALLDATETIME
)
INSERT #foo SELECT 1, '1900-01-01 18:34';
INSERT #foo SELECT 2, '02:25'; -- 1900-01-01 is the default
INSERT #foo SELECT 3, '06:34';
INSERT #foo SELECT 4, '10:25';
INSERT #foo SELECT 5, '14:57';
INSERT #foo SELECT 6, '20:36';
INSERT #foo SELECT 6, '23:11';
-- now let's find those rows where NextBar is within the next 6 hours
DECLARE @.s SMALLDATETIME, @.e SMALLDATETIME;
SET @.s = CURRENT_TIMESTAMP - DATEDIFF(DAY, 0, GETDATE());
SET @.e = DATEADD(HOUR, 6, @.s);
SELECT FooID, NextBar = CONVERT(CHAR(5), NextBar, 108)
FROM #foo
WHERE NextBar >= @.s
AND NextBar <= @.e;
-- see all rows:
SELECT FooID, NextBar = CONVERT(CHAR(5), NextBar, 108)
FROM #foo;
DROP TABLE #foo;|||Figured it out:
select cast(convert(varchar,OurTime,114) as datetime) as OurTime
from FileName
where cast(convert(varchar,OurTime,114) as datetime) >
convert(varchar,getdate(),114)
Thank you all!
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.phx.gbl...
>I need to store the time in a table ("10:00:00 AM") and then compare just
>the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
>this is simple as heck but because I am a newbie I am stumbling. Can
>someone provide me with a sample of this? Thank you.
>|||Figured it out:
select cast(convert(varchar,OurTime,114) as datetime) as OurTime
from FileName
where cast(convert(varchar,OurTime,114) as datetime) >
convert(varchar,getdate(),114)
Thank you all!
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.phx.gbl...
>I need to store the time in a table ("10:00:00 AM") and then compare just
>the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
>this is simple as heck but because I am a newbie I am stumbling. Can
>someone provide me with a sample of this? Thank you.
>|||> select cast(convert(varchar,OurTime,114) as datetime) as OurTime
> from FileName
> where cast(convert(varchar,OurTime,114) as datetime) >
> convert(varchar,getdate(),114)
All these casts and converts will really hurt your performance.
If you're cunning enough to have an index on that column, you should compare
the plan for this solution compared to the one I provided...

Help - Can Execute return a value?

Hello
I'm relatively new to SQL Server/T-SQL and find myself stuck with this
problem:
I need to do something like this:
Declare @.someValue NVarchar(100)
Declare @.someFunction Varchar(100)
--
-- Assign value to @.someValue from a Cursor
--
--
-- Assign name of the function to @.someFunction from a Cursor
--
Declare @.ret NVarchar(100)
Execute 'Select @.ret = ' + @.someFunc + '(''' + @.someValue + ''')'
I expect the last Execute statement to leave the return value from the
function in @.ret.
What I get is
Must declare the variable '@.ret'.
I have also tried
Execute sp_ExecuteSQL 'Select @.ret = ' + @.someFunc + '(''' +
@.someValue + ''')'
with the same result.
Any help with making this work or other ways of doing this will be very
much appreciated!
TIA.
Vamsi.Vamsi,
Use sp_executesql.
Declare @.ret NVarchar(100)
declare @.sql nvarchar(4000)
set @.sql = N'Select @.ret = ' + @.someFunc + '(''' + @.someValue + ''')'
exec sp_executesql @.sql, N'@.ret NVarchar(100) output', @.ret output
print @.ret
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB|||Splendid!!
I still can't understand that one line of code, but, it worked
perfectly :-)
Thanks very much, AMB.
V.|||tvamsidhar (tvamsidhar@.gmail.com) writes:
> Splendid!!
> I still can't understand that one line of code, but, it worked
> perfectly :-)
For more details on sp_executesql and dynamic SQL in general, see
an article on my web site: http://www.sommarskog.se/dynamic_sql.html.
By the way, if you are new to T-SQL, dynamic SQL is probably not where
you should start.
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|||Thanks Erland, I'll study the article! Its already in my Google
bookmarks :)
As for starting with dynamic SQL, I really don't have a choice :-) I
need to be able to run validations and formatting on type-less text
data that should be "interpreted" to be of datatypes defined in a
metadata repository (SQL Server tables) and satisfying validations
(reg. expressions, TSQL functions/SPs, etc) specified in the same
repository.
Running these on the app. server turned out to be way too inefficient
and cumbursome; hence the dynamic SQL. And although I'm more
experienced with PL/SQL, the powers that be insist on using SQL Server
I thank you for the input.
V.