February 19, 2013 at 3:26 am
Hi
I'm trying to create the function below
CREATE FUNCTION checkLock (@van_id varchar , @nextdel DATETIME)
RETURNS varchar
AS
BEGIN
declare @locked as varchar = '0'
declare @count as int
select @van_id + replace(convert(varchar(11), @nextdel, 113), ' ' ,'') datekey from replicationLock
set @count = @@rowcount
if (@count = 0)
begin
set @locked = '0'
end
else
begin
set @locked = '1'
end
return @locked
END
I just keep getting the error
Msg 444, Level 16, State 2, Procedure checkLock, Line 8
Select statements included within a function cannot return data to a client.
Problem is I'm not very knowledgable about sql server functions etc and I've been at this yesterday afternoon and all morning and can not see where I am going wrong or find any solution on the net. I won't bother explaining what my function does as its about as straight forward as it gets. I want to use this function inside an update statement being called from a c# app.
How can I get the row count of the query without using a select? or am I missing something here ?
Any help would be greatly appreciated here as its really holding me up. Thanks.
February 19, 2013 at 3:34 am
Replace
select @van_id + replace(convert(varchar(11), @nextdel, 113), ' ' ,'') datekey from replicationLock
set @count = @@rowcount
with :
SELECT @count = COUNT(@van_id + replace(convert(varchar(11), @nextdel, 113), ' ' ,'')) from replicationLock
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 19, 2013 at 3:34 am
select @van_id + replace(convert(varchar(11), @nextdel, 113), ' ' ,'') datekey from replicationLock
The above line is returning data to the client, which is causing the error
Do
SELECT @count = COUNT(@van_id + replace(convert(varchar(11), @nextdel, 113), ' ' ,'')) from replicationLock
Edit - Abu quick off the draw.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply