Problem with calling Scalar Function - Database Discussion Boards

Select employees haveing thier birthday with in coming 2 weeks

Vipin.d26-Jul-07 5:52

26-Jul-07 5:52

hi all

i have an empoyee table with a column Dateofbith
i need to select the employees whose birthday with in coming 14 days..

how i select it.?
thnks in advance..

All I ever wanted is what others have....
CrazySanker

Re: Select employees haveing thier birthday with in coming 2 weeks

andyharman26-Jul-07 6:43

andyharman

26-Jul-07 6:43

For SQL-Server it would be something like:

select * from Employee
where datepart(dy, BirthDate) between datepart(dy, GetDate()) and datepart(dy, GetDate()) + 14
or 365 + datepart(dy, BirthDate) between datepart(dy, GetDate()) and datepart(dy, GetDate()) + 14

The "or" condition traps the situation where today is in the last couple of weeks of December and the employee's birthday falls at the start of January. You might need to play with the boundary conditions (i.e. the "365" and "14" constants).

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Re: Select employees haveing thier birthday with in coming 2 weeks

Vipin.d27-Jul-07 20:47

Vipin.d

27-Jul-07 20:47

hi,
Thank u for ur reply.

I am using MSSQL 2000.

it shows some error ..

wat you mean by 'dy'??

All I ever wanted is what others have....
CrazySanker

Re: Select employees haveing thier birthday with in coming 2 weeks

Pete O'Hanlon26-Jul-07 8:53

Pete O'Hanlon

26-Jul-07 8:53

You could do this by selecting all records where the dateofbirth was between getdate() and dateadd(dd, getdate(), 14).

Deja View - the feeling that you've seen this post before.

Re: Select employees haveing thier birthday with in coming 2 weeks

Vipin.d27-Jul-07 20:26

Vipin.d

27-Jul-07 20:26

Thank you for ur reply..
I think, there is some logic problem in ur reply.
Means
"where the dateofbirth was between getdate() and dateadd(dd, getdate(), 14)"

Suppose an employe 'Sanker' whose DOB is "29/july/1982"

According ur reply,in select query the getdate value is 27/July/2007 and add 14 to it will return 10/aug/2007

do you think employee 'Sanker' will select from ur query??

All I ever wanted is what others have....
CrazySanker

Re: Select employees haveing thier birthday with in coming 2 weeks

Pete O'Hanlon29-Jul-07 10:15

Pete O'Hanlon

29-Jul-07 10:15

It would.

Deja View - the feeling that you've seen this post before.

Re: Select employees haveing thier birthday with in coming 2 weeks

Vipin.d27-Jul-07 20:48

Vipin.d

27-Jul-07 20:48

I got the solution..

i fell this is very simple one..

select EmployeeID,EmployeeName
from
TblEmployeeMaster
where
datediff
(
dd
,convert(datetime,'1900/'+cast(month(getdate()) as varchar)+'/'+cast (day(getdate()) as varchar),111)
,convert(datetime,'1900/'+cast(month(DateOfBirth) as varchar)+'/'+cast (day(DateOfBirth) as varchar),111)
) between 0 and 14

All I ever wanted is what others have....
CrazySanker

Problem with calling Scalar Function

--miCZar--26-Jul-07 3:55

--miCZar--

26-Jul-07 3:55

Hi there,

I have problem with caling my T-SQL scalar function.

SET ANSI_NULLS ON<br />
SET QUOTED_IDENTIFIER ON<br />
GO<br />
<br />
-- =============================================<br />
-- Author:		<Author,,Name><br />
-- Create date: <Create Date, ,><br />
-- Description:	<Description, ,><br />
-- =============================================<br />
CREATE FUNCTION dbo.func(@param int)<br />
RETURNS bit<br />
AS<br />
BEGIN<br />
	DECLARE @ret bit;<br />
<br />
	IF EXISTS (SELECT id FROM [dbo].myTmpTab WHERE id=@param)<br />
		BEGIN<br />
			set @ret=1;<br />
		END<br />
	ELSE<br />
		BEGIN<br />
			set @ret=0;<br />
		END<br />
	RETURN @ret;<br />
END<br />
GO

I want to call it in C# code, but i cant it even call in MSSQL Server Mamagment Studio (SELECT myTmpDB.dbo.func(1)) and i get this msg:

Msg 557, Level 16, State 2, Line 1<br />
Only functions and extended stored procedures can be executed from within a function.

Is there any1 who know how to solve it? Im quite sure it would work on Sybase as i have done smthin similar about year ago Smile | :)

-------------------
n00b @ pr0grAmiNg

Re: Problem with calling Scalar Function

Blue_Boy26-Jul-07 4:13

Blue_Boy

26-Jul-07 4:13

Make a Google[^]search

I Love SQL

Re: Problem with calling Scalar Function

--miCZar--26-Jul-07 22:25

--miCZar--

26-Jul-07 22:25

I did it

But its useless for me to call function from stored procedure because i cant get my ret value.

-------------------
n00b @ pr0grAmiNg

Re: Problem with calling Scalar Function

andyharman26-Jul-07 6:54

andyharman

26-Jul-07 6:54

Seems to work with SQL-Server 2000 (after I changed the table name to one of mine). Could "func" be a reserved word under 2005?

Re: Problem with calling Scalar Function

--miCZar--26-Jul-07 22:27

--miCZar--

26-Jul-07 22:27

names of tabels and function are changed so thats not an issue. Maybe its caused by the fact DB is on MSSQL 2000 Server? Ill do some research this weekend.

-------------------
n00b @ pr0grAmiNg

Re: Problem with calling Scalar Function

Pete O'Hanlon26-Jul-07 9:41

Pete O'Hanlon

26-Jul-07 9:41

Bizarre - I've copied your code exactly and it works fine for me.

Deja View - the feeling that you've seen this post before.

inserting values by checking the existing values

dsaikrishna26-Jul-07 2:30

dsaikrishna

26-Jul-07 2:30

hi all,
how to handle this case
just i write in this way

insert into e.empone values('kris45','sai','20','prg') where e.empid in (insert into d.deptone values ('20','dev','prg','kris45')
where e.empid in (insert into p.profone values('prg','dev','kris45') where e.empid = d.empid and e.empid = p.deptid

sai krishna

Re: inserting values by checking the existing values

Mubashir Javaid26-Jul-07 2:42

Mubashir Javaid

26-Jul-07 2:42

Get values in store procedure
if not exists(Select * from empone where val1=@val1 and val2=val2 and val3=@val3 and val4=@val4)
then write insert statement.

God Bless you.
Always do good to others

getting second highest date from a sql table

hkchauhan26-Jul-07 2:16

hkchauhan

26-Jul-07 2:16

how can I get second highest date from a sqltable using a query???

hiiiiii

Re: getting second highest date from a sql table

Krish - KP26-Jul-07 2:21

Krish - KP

26-Jul-07 2:21

earlier some time back (in may/jun) i've given this solution for getting nth max value. try the same must work for you

DECLARE @nth as bigint
SET @nth = 10

SELECT * FROM TblA AS a WHERE @nth = (SELECT COUNT(ColA) FROM
TblB AS b WHERE b.ColA >= a.ColA)

@nth is the variable to hold the 1 for getting 1st max, 2 for 2nd max, .....

Regards
KP

Re: getting second highest date from a sql table

hk7792126-Jul-07 2:34

hk77921

26-Jul-07 2:34

run this query
it will return the second highest date

SELECT max(mdate) FROM <tablename> where mdate not in (SELECT max(mdate) FROM <tablename>) order by 1 desc

Harry

inserting values by checking the existing values

dsaikrishna26-Jul-07 2:12

dsaikrishna

26-Jul-07 2:12

Dear all,
i am a novice programmer to sqlserver i just want a condition for how to insert the values into 3 tables of data at a time by considering that the three tables contains values if one of the table does not contain any values then it wont to be inserted into any of the tables

sai krishna

Re: inserting values by checking the existing values

Eliz.k27-Jul-07 0:20

Eliz.k

27-Jul-07 0:20

Not sure if this is what you want...

IF EXISTS (SELECT TOP 1 1 FROM tblTable1 WHERE col1 = 'abc') AND
EXISTS (SELECT TOp 1 1 FROM tblTable2 WHERE col2= 'def') AND
EXISTS ( SELECT TOP 1 1 FROM tblTable3 WHERE col3= 'xyz' )
--Do Something
ELSE
--Do Nothing

Eliz.K
www.oin1.com

Hiding Report Parameters.

Karuna Kumar26-Jul-07 0:34

Karuna Kumar

26-Jul-07 0:34

Hi,

I am integrating Reporting services with my web application.
Fisrt i designed the report and deployed on the report server.My report is using 4 parameters Location,UserID,LastName,Firtsname.I am able to view the report by passing the parameters.This is all done throw report server.
But i am using my own controls(Textboxes) in my web page to pass this parameters.so i paced four text boxes and a ReportViewer Control.i am able to bind this parameters with the parametrs on report server.
but when i run the page the reportviewe control also showing the parametrs of report server.I dont need this because i already using my TextBoxes.so how to hide the reportserver parameters on Reportviwercontrol.

I hope Ypu guys understood my question. Simply i want hide report server parameters on reportviewer control

Plaese can any body help me?

Thanks
Karuna

Karuna

Stored Procedure Issue

Brendan Vogt26-Jul-07 0:00

Brendan Vogt

26-Jul-07 0:00

Hi,

I have a search form in ASP.NET 1.1. The search has 4 input textboxes, but only one of these are required.

Now I have to do a SELECT in stored procedure. I'm not sure how to construct this stored procedure because not all the input values will have a value.

Please can someone help me with my confusion.

Regards
ma se

Re: Stored Procedure Issue

Mike Dimmick26-Jul-07 0:46

Mike Dimmick

26-Jul-07 0:46

I would strongly recommend constructing a parameterized query in your client code and using that, rather than a stored procedure.

You occasionally find recommendations to use 'OR @paramName IS NULL' to make a parameter optional. This works in the sense that the correct results are returned, but it causes problems for the query optimizer. In SQL Server 2000 SP4, I observed that for a query that looked like

SELECT * FROM table
WHERE 
   ( ColumnA = @columnA OR @columnA IS NULL ) AND
   ( ColumnB = @columnB OR @columnB IS NULL )

that the optimizer actually picked an index on ColumnB when you specified only @columnA, and one on ColumnA when you specified only @columnB! That is, it scanned the wrong index, rather than seeking on the right one.

SQL Server 2005 at least picked the right index and index operation, but you have a new problem: the query plan for a stored procedure is computed the first time it is run, based on the arguments supplied for that run, and the resulting procedure is cached and reused until it ages out of the cache. If, the first time, you supply column A but not B, it picks the index on column A and caches this plan. If you then supply column B but not A, it tries to use the cached plan, eventually realises that the plan isn't workable and reverts to scanning the index and looking up in the base table - just doing a table scan would have been quicker. You can work around this by specifying WITH RECOMPILE on an EXEC statement, which recompiles the procedure just for that run (the previously cached plan is retained), or by specifying WITH RECOMPILE on the CREATE PROCEDURE statement, which means it's recompiled every time.

If you must use a stored procedure, use it as a shell - call sp_executesql with a dynamically-constructed query. The reason I recommend doing it in the client code is that C# and VB's string manipulation support is much better than T-SQL's.

Stability. What an interesting concept. -- Chris Maunder

Re: Stored Procedure Issue

Colin Angus Mackay26-Jul-07 0:52

Colin Angus Mackay

26-Jul-07 0:52

Gosh! That was a much more comprehensive answer than the one I gave.

My only comment would be:

Mike Dimmick wrote:
call sp_executesql with a dynamically-constructed query. The reason I recommend doing it in the client code is that C# and VB's string manipulation support is much better than T-SQL's.

The string manipulation may be better in the client code, but you open up some security concerns, in my view. I prefer the database to be the master of the queries run on it and allowing dynamic code through from the client shoots that idea down. Building the dyanmic code in SQL at least adds the possibility of running additional checks and knowing that the SQL is being built internally from a more trusted source.

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...

"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."

My website

Re: Stored Procedure Issue

Colin Angus Mackay26-Jul-07 0:48

Colin Angus Mackay

26-Jul-07 0:48

ma se wrote:
I'm not sure how to construct this stored procedure because not all the input values will have a value.

Then they will be null?

SELECT * FROM MyTable
WHERE (someColumn = @param1 OR @param1 IS NULL) AND
      (someOtherColumn = @param2 OR @param2 IS NULL) AND... so on

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.