Hi, I have a problem with my sql function.

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

Hi, I have a problem with my sql function.

Author

Topic

aplimanmoe
Starting Member

2 Posts

Posted - 2015-04-27 : 10:20:02

Hi, I am new and I need help with my sql Function.

I writing this but there def something wrong in it, Help please

GO
CREATE FUNCTION MCA_Activation
(@SubscriberNumber Varchar(15),
@startingdate datetime,
@subscribertype Varchar(20),
@subscriberlanguage Varchar(20))
RETURNs int

AS
BEGIN
declare @Sub_No as char(15)
declare @Sub_No2 as char(15)

select @Sub_No = Subscriber_Number from subscribers_settings where Subscriber_Number = @SubscriberNumber

if @SubscriberNumber = @Sub_No update subscribers_settings set ApliClip_Registered = 1,subscriber_type=@subscribertype,subscriber_language=@subscriberlanguage where Subscriber_Number=@SubscriberNumber;
else

insert into subscribers_settings (Subscriber_Number,apliclip_registered,subscriber_type,subscriber_language) values (@SubscriberNumber,1,@subscribertype,@subscriberlanguage);
end
select @sub_no2 = subscriber_number from subscribers_settings where subscriber_number = @SubscriberNumber

if @SubscriberNumber = @Sub_No2 update apliclip_subscribers set starting_date=startingdate where Subscriber_Number=SubscriberNumber;

else
insert into apliclip_subscribers (Subscriber_Number,Starting_Date) values (@subscribernumber, @startingdate)

end

return 1

end

_______________

Thank you

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-27 : 10:33:49

If you format your code properly the errors become obvious (I used poorsql.com)




--WARNING! ERRORS ENCOUNTERED DURING SQL PARSING! 
CREATE FUNCTION MCA_Activation (
	@SubscriberNumber VARCHAR(15)
	,@startingdate DATETIME
	,@subscribertype VARCHAR(20)
	,@subscriberlanguage VARCHAR(20)
	)
RETURNS INT
AS
BEGIN
	DECLARE @Sub_No AS CHAR(15)
	DECLARE @Sub_No2 AS CHAR(15)

	SELECT @Sub_No = Subscriber_Number
	FROM subscribers_settings
	WHERE Subscriber_Number = @SubscriberNumber

	IF @SubscriberNumber = @Sub_No
		UPDATE subscribers_settings
		SET ApliClip_Registered = 1
			,subscriber_type = @subscribertype
			,subscriber_language = @subscriberlanguage
		WHERE Subscriber_Number = @SubscriberNumber
	ELSE
		INSERT INTO subscribers_settings (
			Subscriber_Number
			,apliclip_registered
			,subscriber_type
			,subscriber_language
			)
		VALUES (
			@SubscriberNumber
			,1
			,@subscribertype
			,@subscriberlanguage
			);
END  -------------------> No RETUEN before the END statement

--------------------What is this stuff here?  If part of the function, why the preceding END?
SELECT @sub_no2 = subscriber_number
FROM subscribers_settings
WHERE subscriber_number = @SubscriberNumber

IF @SubscriberNumber = @Sub_No2
	UPDATE apliclip_subscribers
	SET starting_date = startingdate
	WHERE Subscriber_Number = SubscriberNumber; ------------------------> YOu have a semicolon followed by ELSE
ELSE
	INSERT INTO apliclip_subscribers (
		Subscriber_Number
		,Starting_Date
		)
	VALUES (
		@subscribernumber
		,@startingdate
		)
	END

RETURN 1 END

aplimanmoe
Starting Member

2 Posts

Posted - 2015-04-27 : 10:37:47

Thank you I will look it up right now

quote:

Originally posted by gbritton

If you format your code properly the errors become obvious (I used poorsql.com)




--WARNING! ERRORS ENCOUNTERED DURING SQL PARSING! 
CREATE FUNCTION MCA_Activation (
	@SubscriberNumber VARCHAR(15)
	,@startingdate DATETIME
	,@subscribertype VARCHAR(20)
	,@subscriberlanguage VARCHAR(20)
	)
RETURNS INT
AS
BEGIN
	DECLARE @Sub_No AS CHAR(15)
	DECLARE @Sub_No2 AS CHAR(15)

	SELECT @Sub_No = Subscriber_Number
	FROM subscribers_settings
	WHERE Subscriber_Number = @SubscriberNumber

	IF @SubscriberNumber = @Sub_No
		UPDATE subscribers_settings
		SET ApliClip_Registered = 1
			,subscriber_type = @subscribertype
			,subscriber_language = @subscriberlanguage
		WHERE Subscriber_Number = @SubscriberNumber
	ELSE
		INSERT INTO subscribers_settings (
			Subscriber_Number
			,apliclip_registered
			,subscriber_type
			,subscriber_language
			)
		VALUES (
			@SubscriberNumber
			,1
			,@subscribertype
			,@subscriberlanguage
			);
END  -------------------> No RETUEN before the END statement

--------------------What is this stuff here?  If part of the function, why the preceding END?
SELECT @sub_no2 = subscriber_number
FROM subscribers_settings
WHERE subscriber_number = @SubscriberNumber

IF @SubscriberNumber = @Sub_No2
	UPDATE apliclip_subscribers
	SET starting_date = startingdate
	WHERE Subscriber_Number = SubscriberNumber; ------------------------> YOu have a semicolon followed by ELSE
ELSE
	INSERT INTO apliclip_subscribers (
		Subscriber_Number
		,Starting_Date
		)
	VALUES (
		@subscribernumber
		,@startingdate
		)
	END

RETURN 1 END

Kristen
Test

22859 Posts

Posted - 2015-04-27 : 13:06:26

Is END perhaps supposed to "match" the IF and ELSE that precede it? (rather than END the Function)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-28 : 02:24:48

You can't do updates in a function. No side-effects are normally allowed in a function.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources