SQL Function Help Please - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Function Help Please

Author

Topic

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-02-26 : 05:26:56

Hiya chaps

I have a table called movements which tracks the issuing of safety wear etc.
movement_id int (identity yes)
emp_no char(8)
clothing_id int (fk)
create_dte smalldatetime
create_by_emp_no char(8)
clothing_price decimal

I need to create a function that will check first that a user has or has not received an item (param clothing_id)
if they have not then insert a new entry.
else
If they have had an item that matches the clothing_id param - check if it is within one year (if it is then do not insert) if the user has had this item and it was longer than a year insert the item.

Ideally I would like the function to return a 1 if a new record was inserted (for the first time)

a 2 if a record was inserted(user has had this item longer than a year ago)

a 3 if user has this item within one year (no record inserted)

Dont know if this is feasable as I am new to transact sql. Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-26 : 05:30:55

You can not insert record to physical table from within UDF. Use Stored proc instead.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-02-26 : 05:36:21

Sorry I dont understand. I am looking to get help to create a function (stored procedure that will return a value)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 05:46:15

[code]CREATE PROCEDURE CheckValues
@param_clothing_id int,
@returnVal int=0 OUTPUT
AS

IF EXISTS (SELECT * FROM movements WHERE clothing_id =param_clothing_id )
BEGIN
INSERT INTO movements (fields)
Values

SELECT @returnval=1
END

IF NOT EXISTS (SELECT * FROM movements WHERE clothing_id =@param_clothing_id )
BEGIN
INSERT INTO movements (fields)
Values

SELECT @returnval=1
END

IF EXISTS (SELECT * FROM movements WHERE clothing_id =@param_clothing_id )
AND NOT EXISTS (SELECT * FROM movements WHERE clothing_id =@param_clothing_id AND created_date> DATEADD(y,-1,GETDATE()) )
BEGIN
INSERT INTO movements (fields)
Values

SELECT @returnval=2
END
IF EXISTS (SELECT * FROM movements WHERE clothing_id =@param_clothing_id AND created_date> DATEADD(y,-1,GETDATE()) )
BEGIN
SELECT @returnval=3
END

GO[/code]

execute this as:-

DECLARE @Var int
Exec CheckValues value for clotingid,@Var OUTPUT
SELECT @Var

to get return value

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-02-26 : 05:50:20

quote:
Originally posted by WelshPunk

Sorry I dont understand. I am looking to get help to create a function (stored procedure that will return a value)

Please note that the functions and stored procedures are slightly different. Find the differences below:

1.Functions return only a scalar(single) values. Note that this single value could be a table variable.

2.You cannot use stored procedure in a select statement like
select <spname > but this is possible with UDF.

3.You can not return a table type variable with stored procedure but this is possible with user defined functions in sql server.

4.Stored procedure may or may not return a value but a function has to return a value.

5.UDFs can accept a smaller number of parameters than stored procedures. UDFs can have up to 1024 parameters, whereas stored procedures support up to 2100 parameters. This is a relatively minor limitation because most routines require a much smaller number of parameters.

6.UDFs cannot call stored procedures (except extended procedures), whereas stored procedures can call other procedures.

7.You can not insert record to physical table from within UDF. You need to use Stored proc instead( From Harsh).

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-02-26 : 07:10:41

quote:

Originally posted by visakh16

CREATE PROCEDURE CheckValues
@param_clothing_id int,
@returnVal int=0 OUTPUT
AS

IF EXISTS (SELECT * FROM movements  WHERE clothing_id =param_clothing_id )
BEGIN
INSERT INTO movements (fields)
Values

SELECT @returnval=1
END

IF NOT EXISTS (SELECT * FROM movements  WHERE clothing_id =@param_clothing_id )
BEGIN
INSERT INTO movements (fields)
Values

SELECT @returnval=1
END

IF EXISTS (SELECT * FROM movements  WHERE clothing_id =@param_clothing_id ) 
AND NOT EXISTS (SELECT * FROM movements  WHERE clothing_id =@param_clothing_id AND created_date> DATEADD(y,-1,GETDATE()) )
BEGIN
INSERT INTO movements (fields)
Values

SELECT @returnval=2
END
IF EXISTS (SELECT * FROM movements  WHERE clothing_id =@param_clothing_id AND created_date> DATEADD(y,-1,GETDATE()) )
BEGIN
SELECT @returnval=3
END

GO

execute this as:-

DECLARE @Var int
Exec CheckValues value for clotingid,@Var OUTPUT
SELECT @Var

to get return value

Hi again I am using dot net (c#) to call this procedure so If I call the ammended procedure

CREATE PROCEDURE [DBO].[CheckValues]

@param_clothing_id int,
@empNo char(8),
@createdBy char(8),
@price decimal,
@qty int,
@returnVal int=0 OUTPUT
AS

SET NOCOUNT ON

IF EXISTS (SELECT * FROM movement WHERE clothing_id =@param_clothing_id )
BEGIN

INSERT INTO movement
(emp_no,
clothing_id,
qty,
create_by_emp_no,
clothing_price)

VALUES(
@empNo,
@param_clothing_id,
@qty,
@createdBy,
@price)

SELECT @returnval=1
END

IF NOT EXISTS (SELECT * FROM movements WHERE clothing_id =@param_clothing_id )
BEGIN

INSERT INTO movement
(emp_no,
clothing_id,
qty,
create_by_emp_no,
clothing_price)

VALUES(
@empNo,
@param_clothing_id,
@qty,
@createdBy,
@price)
SELECT @returnval=1
END

IF EXISTS (SELECT * FROM movements WHERE clothing_id =@param_clothing_id )
AND NOT EXISTS (SELECT * FROM movements WHERE clothing_id =@param_clothing_id AND created_date> DATEADD(y,-1,GETDATE()) )

BEGIN

INSERT INTO movement
(emp_no,
clothing_id,
qty,
create_by_emp_no,
clothing_price)

VALUES(
@empNo,
@param_clothing_id,
@qty,
@createdBy,
@price)

SELECT @returnval=2
END
IF EXISTS (SELECT * FROM movements WHERE clothing_id =@param_clothing_id AND created_date> DATEADD(y,-1,GETDATE()) )
BEGIN
SELECT @returnval=3
END
GO

Will it return the return codes on the insertions or fail to insert. Or do I need to create another sp that calls this sp?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 07:20:37

Nope. You can get output variables return values in your .NEt code.see this:-
http://aspnet.4guysfromrolla.com/articles/062905-1.aspx

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-02-26 : 07:59:21

Hi there I have managed to get this procedure to work, however after creating a new record for a user that has not received a (clothing_id)to date, it allowed me to create another which should fail as one record exists within one year from today. The rule should be

If no item exists at all for user
Insert new record
If user has received this item (within one year)
Do not insert the record as users are allowed one item per year
Else
they have received this item (but longer than a year) so insert the item

Sorry my mistake. Doh! Probably not in the remit of this forum but does anyone know how to get the @returnVal in c#

Regards Phill

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 10:39:52

Did you refer link i sent you. i thought that an example to illustrate how you get back value from SP. refer this too:-

http://www.sqlteam.com/article/stored-procedures-returning-data

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-02-26 : 11:16:36

Right on the button matey! Thank you very much. Been looking for a good example like this for a couple of hours. Thanks

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-02-27 : 06:59:53

Hey chaps I am still having some problems here. Here is the logic of what I need @empNo =111 clothing_id = 1. I have ammended the SP to use in query analyzer for clarity

use safety_clothing

SET NOCOUNT ON
DECLARE @msg varchar(200)
DECLARE @returnVal int

/*First check if the record already exists */
IF EXISTS (SELECT * FROM movement WHERE emp_no =00116712 AND clothing_id=1 )
BEGIN
/*Record already exists - So now check if it is less than a year old */
SET @msg='Record exists already'
PRINT @msg

IF EXISTS (SELECT * FROM movement WHERE emp_no =00116712 AND clothing_id =1 AND DATEDIFF(yy,create_dte,GETDATE())<=1 )
BEGIN

/*Record is less than a year old so return a 3*/
SET @msg='Record exists and is less than one year old so return a 3'
PRINT @msg

SELECT @returnval=3
END
ELSE

/*Record older than a year so insert the record and return a 2 */

BEGIN
SET @msg='Record exists and is greater than one year old so return a 2 and insert the record'
PRINT @msg
SELECT @returnval=2
END

END
ELSE

BEGIN
/*Record does not exist at all - So insert the new record and return a 1 */
SET @msg='Record does not exist at all so insert the new record and return a 1'
PRINT @msg

SELECT @returnval=1
END
GO

I have removed all the inserts for clarity purposes. There is a problem though with the logic. Whenever there are no records for this user and clothing_id it works ok and returns a 1. If I then change the date of the inserted record to say 01/01/2007 (this makes it older than a year) and run the same thing again in query analyzer using the same input criteria as before - it should return a 2. However it returns a code three saying that the record is less than a year old (when it is longer than a year old). If I change the code to If Not Exists - it will put the record in correctly - but then if I try to insert another using the same criteria it inserts another when this time it should fail as there exists a record within one year. Can anyone help here please as I am going nuts. Regards Phill

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-02-27 : 07:38:44

Apologies to Visakh. I cant even copy code properly doh! Thanks for your help Visakh - most appreciated.

Regards Phill

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-27 : 11:37:30

quote:
Originally posted by WelshPunk

Apologies to Visakh. I cant even copy code properly doh! Thanks for your help Visakh - most appreciated.

Regards Phill

No worries WelshPunk

. Glad that i could help you out. Feel free to post whenever you've a question. Would be always happy to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources