PeterBaileyUk
asked on
Sql server function help
I have an existing access query which if returned rows signifies match true
I am trying to create a function in sql server that tells me if the client code I pass is matched.
Ive sort of built up the query in a similar method I have been shown by experts.
All i need to do is pass the table name and pk name so i can apply the function to all clients.
I dont know how to determine the logic. ie if rows return x else other
The access string above shows my intention. also I dont know how to call it to test it
the function code so far:
StrSelect = "SELECT [AbiToMvris _NEW_].Abi1"
--StrFrom = " FROM [AbiToMvris _NEW_]"
--StrWhere = " WHERE ((([AbiToMvris _NEW_].Abi1)='" & ClientCode & "') AND (([AbiToMvris _NEW_].[MVRIS CODE]) Is Not Null));"
I am trying to create a function in sql server that tells me if the client code I pass is matched.
Ive sort of built up the query in a similar method I have been shown by experts.
All i need to do is pass the table name and pk name so i can apply the function to all clients.
I dont know how to determine the logic. ie if rows return x else other
The access string above shows my intention. also I dont know how to call it to test it
the function code so far:
Use ClientData
go
CREATE FUNCTION IsMatched(
@TableName sysname,
@PKName sysname,
@CWName sysname
) RETURNS BIT
AS
DECLARE @SQL AS VARCHAR(MAX) =''
SET @SQL = '
SELECT [' +@TableName + '].[' +@PKName +']' + '
FROM [' + @TableName+ ']'+'
WHERE ((([' +@TableName +'].[' +@PKName + '])=['+@TableName + '].[' +@PKName +'] AND (([' +@TableName + '].[' +@CWName ']) IS Not Null));'
--if the result of the query has rows then ismatched =1 else 0
PRINT @SQL
RETURN 1
Use the @@ROWCOUNT to check if the query returns records or not:
CREATE FUNCTION IsMatched(
@TableName sysname,
@PKName sysname,
@CWName sysname
) RETURNS BIT
AS
DECLARE @SQL AS VARCHAR(MAX) =''
SET @SQL = '
SELECT [' +@TableName + '].[' +@PKName +']' + '
FROM [' + @TableName+ ']'+'
WHERE ((([' +@TableName +'].[' +@PKName + '])=['+@TableName + '].[' +@PKName +'] AND (([' +@TableName + '].[' +@CWName ']) IS Not Null));'
EXEC(@SQL)
IF @@ROWCOUNT > 0
RETURN 1
ELSE
RETURN 0
--if the result of the query has rows then ismatched =1 else 0
--PRINT @SQL
Hi Peter,
Usage for my function will be like below-
Hope it helps!
Usage for my function will be like below-
SELECT dbo.IsMatched('val1', 'val2'.....)
Hope it helps!
ASKER
ive got this message Msg 156, Level 15, State 1, Procedure IsMatched, Line 11 [Batch Start Line 2]
Incorrect syntax near the keyword 'DECLARE'.
may need a way to run the function to see what sql it produces in case I mucked up the string i have added a + in front of the last @cwname
Incorrect syntax near the keyword 'DECLARE'.
may need a way to run the function to see what sql it produces in case I mucked up the string i have added a + in front of the last @cwname
Use ClientData
go
CREATE FUNCTION IsMatched(
@TableName sysname,
@PKName sysname,
@CWName sysname
) RETURNS BIT
AS
DECLARE @SQL AS VARCHAR(MAX) =''
SET @SQL = '
SELECT [' +@TableName + '].[' +@PKName +']' + '
FROM [' + @TableName+ ']'+'
WHERE ((([' +@TableName +'].[' +@PKName + '])=['+@TableName + '].[' +@PKName +'] AND (([' +@TableName + '].[' +@CWName +']) IS Not Null));'
EXEC(@SQL)
IF @@ROWCOUNT > 0
RETURN 1
ELSE
RETURN 0
Brackets issue , use below-
SET @SQL = '
SELECT [' +@TableName + '].[' +@PKName +']' + '
INTO #Temps
FROM [' + @TableName+ ']'+'
WHERE [' +@TableName +'].[' +@PKName + ']=['+@TableName + '].[' +@PKName +'] AND [' +@TableName + '].[' +@CWName +'] IS Not Null'
EXEC (@SQL)
Also check this - I dont this you can do this-
Msg 443, Level 16, State 14, Procedure IsMatched, Line 21
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
Msg 443, Level 16, State 14, Procedure IsMatched, Line 21
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
Sorry Peter, I didn't test it.
Now after testing I came with this version:
Now after testing I came with this version:
CREATE FUNCTION IsMatched
(
@TableName sysname,
@PKName sysname,
@CWName sysname
) RETURNS BIT
AS
BEGIN
DECLARE @SQL AS NVARCHAR(MAX)
SET @SQL = N'
SELECT [' +@TableName + '].[' +@PKName +']' + '
FROM [' + @TableName+ ']'+'
WHERE ((([' +@TableName +'].[' +@PKName + '])=['+@TableName + '].[' +@PKName +'] AND (([' +@TableName + '].[' +@CWName + ']) IS Not Null));'
exec sp_executesql @SQL
IF @@ROWCOUNT > 0
RETURN 1;
RETURN 0
END
Please create an SP like below-
Usage
Hope it helps!
CREATE PROC IsMatched
(
@TableName sysname,
@PKName sysname,
@CWName sysname
)
AS
BEGIN
DECLARE @SQL AS VARCHAR(MAX) = ''
SET @SQL = '
SELECT [' +@TableName + '].[' +@PKName +']' + '
INTO #Temps
FROM [' + @TableName+ ']'+'
WHERE [' +@TableName +'].[' +@PKName + ']=['+@TableName + '].[' +@PKName +'] AND [' +@TableName + '].[' +@CWName +'] IS Not Null'
EXEC (@SQL)
IF EXISTS (SELECT TOP 1 1 FROM #Temps)
SELECT 1
ELSE
SELECT 0
END
Usage
DECLARE @ret INT
EXEC @ret = IsMatched('val1','va2'..)
PRINT @ret
Hope it helps!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Agree with Guy. Thats what i mentioned in earlier posts.
Msg 443, Level 16, State 14, Procedure IsMatched, Line 21
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
Msg 443, Level 16, State 14, Procedure IsMatched, Line 21
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
a starter tutorial example on EE:
https://www.experts-exchange.com/articles/18299/SQL-CLR-Net-Integration-in-2015-year-not-product-version.html
https://www.experts-exchange.com/articles/18299/SQL-CLR-Net-Integration-in-2015-year-not-product-version.html
ASKER
ok I will ditch that idea and populate the field from vb.net elsewhere. the ismatched field at present must be populated how do i alter it to allow null.
I tried the manual way but sql express wont let me change it.
I tried the manual way but sql express wont let me change it.
I tried the manual way but sql express wont let me change it.Run the following T-SQL command:
ALTER TableName
ALTER ColumnName BIT NULL
Keywords missing. Now should be the correct:
ALTER TABLE TableName
ALTER COLUMN ColumnName BIT NULL
Hi Peter,
I gave you the idea with the error message, modified your code to sp and points to some one else?
Also check this - I dont this you can do this-
Msg 443, Level 16, State 14, Procedure IsMatched, Line 21
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
I gave you the idea with the error message, modified your code to sp and points to some one else?
Also check this - I dont this you can do this-
Msg 443, Level 16, State 14, Procedure IsMatched, Line 21
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
Please use like below-
Open in new window
Hope it helps!