April 4, 2010 at 8:23 am
Hi,
I've this script that i need to check the current hour and if it is in the current time in the variable do something.
I've this script and i've problem with the convert.
is it possible to work with this scrip?
THX
declare @param1 nvarchar(10)
set @param1 = '2,4,6'
declare @current_datetime datetime
set @current_datetime = getdate()
declare @get_min smallint
select @get_min = datepart(mi,@current_datetime)
if @get_min in (@param1)
begin
print 'do something'
end
April 4, 2010 at 11:29 pm
declare @param1 nvarchar(10)
set @param1 = '2,4,6'
--> you can also add desired values to @table variable instead of setting value of veriable.
declare @current_datetime datetime
set @current_datetime = getdate()
declare @get_min smallint
select @get_min = datepart(mi,@current_datetime)
if @get_min in (select 2 union select 4 union select 6)
begin
print 'do something'
end
April 4, 2010 at 11:51 pm
If hard coded UNION statement is used, then whats the need for the @param1 variable here.
John
April 5, 2010 at 12:15 am
You did not consider comments added by me.
--> you can also add desired values to @table variable instead of setting value of veriable.
You dont need to add vairable, use veriable table and insert your input hour/minutes values to this table to use as a parameter and pass it to IN statement.
April 5, 2010 at 12:51 am
flash.rsn (4/5/2010)
You dont need to add vairable, use veriable table and insert your input hour/minutes values to this table to use as a parameter and pass it to IN statement.
Hi,
Flash point also good,
But for OP!!!
Declare @param1 nvarchar(10),
@sql nvarchar(max)
set @param1 = '2,4,6'
set @sql = 'if (datepart(mi,getdate()) in ('+@param1+'))'+
'begin
print ''do something''
end '+
'else
print ''DONT'''
exec sp_executesql @sql
April 5, 2010 at 1:12 am
yes look like good
April 5, 2010 at 7:12 am
Another approach
CREATE FUNCTION [dbo].[uf_utl_SplitNString]
(
@InStr nvarchar(max) = null ,
@token nvarchar(4000) = ','
)
RETURNS @RtnElement TABLE ( item nvarchar(4000))
AS
BEGIN
declare @pos int, @tokenlen int, @InstrLen int
declare @ThisStr nvarchar(max)
set @InStr = replace(@InStr,'"','""') -- to escape the single/double quote
set @tokenlen = case @token when ' ' then 1 else len(@token) end
set @InStr = @InStr + @token
while len(@InStr) > 0
begin
set @InstrLen = len(@Instr)
set @pos = charindex(@token, @InStr )
set @ThisStr = left(@InStr, @pos -1 )
set @InStr = substring(@InStr, @pos+@tokenlen, @InstrLen - @pos)
insert @RtnElement (item) select case when len(@ThisStr) > 0 then @ThisStr else null end
end
return
end
--------------------------------------------
declare @param1 nvarchar(10)
set @param1 = '29,7,8'
declare @current_datetime datetime
set @current_datetime = '2010-04-05 18:29:29.730'
declare @get_min smallint
select @get_min = datepart(mi,@current_datetime)
if @get_min IN (select ITEM from dbo.[uf_utl_SplitNString] (@param1, ','))
begin
print 'do something'
end
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 5, 2010 at 9:50 am
Personally, if possible, I like the idea of storing the list of values in a table better because it ends up being more flexible. But if that doesn't work there's no reason to split the parameter into a table. The following should do what you need. I'm storing @get_min as a varchar which, if it's used later in the script, may not work as expected. You can use a smallint and then convert it to a varchar in the charindex if that's the case. I do want to note that your post stated that you're trying to work on hour but the script is getting minutes. You can use hh as the datepart to get hours instead.
declare @param1 nvarchar(10)
set @param1 = '2,4,6'
declare @current_datetime datetime
set @current_datetime = getdate()
declare @get_min varchar(2)
select @get_min = datepart(mi,@current_datetime)
if charindex(@get_min,@param1) > 0
begin
print 'do something'
end
April 5, 2010 at 8:49 pm
cfradenburg (4/5/2010)
Personally, if possible, I like the idea of storing the list of values in a table better because it ends up being more flexible. But if that doesn't work there's no reason to split the parameter into a table. The following should do what you need. I'm storing @get_min as a varchar which, if it's used later in the script, may not work as expected. You can use a smallint and then convert it to a varchar in the charindex if that's the case. I do want to note that your post stated that you're trying to work on hour but the script is getting minutes. You can use hh as the datepart to get hours instead.
declare @param1 nvarchar(10)set @param1 = '2,4,6'
declare @current_datetime datetime
set @current_datetime = getdate()
declare @get_min varchar(2)
select @get_min = datepart(mi,@current_datetime)
if charindex(@get_min,@param1) > 0
begin
print 'do something'
end
Excellent thinking but not complete. If the current minute is 2 and the parameter is '4,8,12', you'll have a bit of a problem on your hands.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2010 at 8:54 pm
Here's the necessary tweek to cfradenburg's good code to make it work without surprises...
declare @param1 nvarchar(10)
set @param1 = '4,8,12'
declare @current_datetime datetime
set @current_datetime = getdate()
declare @get_min varchar(2)
select @get_min = datepart(mi,@current_datetime)
if patindex('%,'+@get_min+',%',','+@param1+',') > 0
begin
print 'do something'
end
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2010 at 9:47 pm
Bhuvnesh (4/5/2010)
Another approachCREATE FUNCTION [dbo].[uf_utl_SplitNString]
(
@InStr nvarchar(max) = null ,
@token nvarchar(4000) = ','
)
RETURNS @RtnElement TABLE ( item nvarchar(4000))
AS
BEGIN
declare @pos int, @tokenlen int, @InstrLen int
declare @ThisStr nvarchar(max)
set @InStr = replace(@InStr,'"','""') -- to escape the single/double quote
set @tokenlen = case @token when ' ' then 1 else len(@token) end
set @InStr = @InStr + @token
while len(@InStr) > 0
begin
set @InstrLen = len(@Instr)
set @pos = charindex(@token, @InStr )
set @ThisStr = left(@InStr, @pos -1 )
set @InStr = substring(@InStr, @pos+@tokenlen, @InstrLen - @pos)
insert @RtnElement (item) select case when len(@ThisStr) > 0 then @ThisStr else null end
end
return
end
--------------------------------------------
declare @param1 nvarchar(10)
set @param1 = '29,7,8'
declare @current_datetime datetime
set @current_datetime = '2010-04-05 18:29:29.730'
declare @get_min smallint
select @get_min = datepart(mi,@current_datetime)
if @get_min IN (select ITEM from dbo.[uf_utl_SplitNString] (@param1, ','))
begin
print 'do something'
end
Bhuvnesh,
I'm not taking a jab at you... I have to ask, though... how often do you find it necessary to split something longer than 8K bytes? There's a reason I'm asking and I promise to make it worth your while.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2010 at 12:04 am
Jeff Moden (4/5/2010)
Bhuvnesh,I'm not taking a jab at you... I have to ask, though... how often do you find it necessary to split something longer than 8K bytes? There's a reason I'm asking and I promise to make it worth your while.
Frankly telling i never think about avoiding this approach unless query is not behaving
badly.here i dont think it will put any bad impact(in my opinion).
Moreover sometimes we use this function to split the data and put it in a table ( temp/permanent) to use it in join in further code( we get data in comma separated form as param in stored proc.).
Is that what you are asking from me?
I will be glad to learn something from you about this approach(function) here 🙂
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 6, 2010 at 8:24 am
Jeff, thanks for catching that. Any particular reason that you switched from charindex to patindex? Charindex can handle multiple characters and I'm assuming there's a reason you decided to change it to patindex.
May 27, 2010 at 4:08 pm
arun.sas (4/5/2010)
flash.rsn (4/5/2010)
You dont need to add vairable, use veriable table and insert your input hour/minutes values to this table to use as a parameter and pass it to IN statement.Hi,
Flash point also good,
But for OP!!!
Declare @param1 nvarchar(10),@sql nvarchar(max)
set @param1 = '2,4,6'
set @sql = 'if (datepart(mi,getdate()) in ('+@param1+'))'+
'begin
print ''do something''
end '+
'else
print ''DONT'''
exec sp_executesql @sql
this code looks good.
but what if i need to adjust it to something like this.
how i can do it?
Declare @param1 nvarchar(30),
@sql nvarchar(max)
set @param1 = 'master,model'
set @sql = 'select name from sys.databases where name not in ('+@param1+')'
exec sp_executesql @sql
June 5, 2010 at 7:57 am
Bhuvnesh (4/6/2010)
Jeff Moden (4/5/2010)
Bhuvnesh,I'm not taking a jab at you... I have to ask, though... how often do you find it necessary to split something longer than 8K bytes? There's a reason I'm asking and I promise to make it worth your while.
Frankly telling i never think about avoiding this approach unless query is not behaving
badly.here i dont think it will put any bad impact(in my opinion).
Moreover sometimes we use this function to split the data and put it in a table ( temp/permanent) to use it in join in further code( we get data in comma separated form as param in stored proc.).
Is that what you are asking from me?
I will be glad to learn something from you about this approach(function) here 🙂
Sorry... lost track of this thread.
No... what I was asking was how often do you have to split something longer than 8k? The reason I'm asking is because just by using MAX instead of 8000, you cause the split to run twice as slow no matter what. It may not make a difference to you because of low rowcounts but it does make a huge difference when you have to work with millions of rows.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply