Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

Sql server function help

I have an existing access query which if returned rows signifies match true

StrSelect = "SELECT [AbiToMvris _NEW_].Abi1"
        --StrFrom = " FROM [AbiToMvris _NEW_]"
        --StrWhere = " WHERE ((([AbiToMvris _NEW_].Abi1)='" & ClientCode & "') AND (([AbiToMvris _NEW_].[MVRIS CODE]) Is Not Null));"

Open in new window


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

Open in new window

Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi Peter,
Please use like below-

Use ClientData
go

CREATE FUNCTION IsMatched
(
	@TableName sysname,
	@PKName sysname,
	@CWName sysname

) 
RETURNS INT
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)
		RETURN 1
	ELSE
		RETURN 0
END

Open in new window


Hope it helps!
Avatar of Vitor Montalvão
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

Open in new window

Hi Peter,
Usage for my function will be like below-

SELECT dbo.IsMatched('val1', 'val2'.....)

Open in new window


Hope it helps!
Avatar of PeterBaileyUk
PeterBaileyUk

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

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

Open in new window

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)

Open in new window

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.
Sorry Peter, I didn't test it.
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

Open in new window

Please create an SP like below-

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

Open in new window


Usage


DECLARE @ret INT   

EXEC @ret = IsMatched('val1','va2'..)

PRINT @ret

Open in new window


Hope it helps!
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
Run the following T-SQL command:
ALTER TableName
    ALTER ColumnName BIT NULL

Open in new window

Keywords missing. Now should be the correct:
ALTER TABLE TableName
    ALTER COLUMN ColumnName BIT NULL

Open in new window

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.