if Exists

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » if Exists

if Exists

Rate Topic

Display Mode

Topic Options

Author

Message

patelmohamad

Posted Tuesday, February 15, 2011 1:33 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:18 AM
Points: 52, Visits: 685

Hi everybody,

this is the normal query through this we will come to know whiether the records exists or not
if EXISTS(SELECT Sr_No FROM #Temp_Table_Col WHERE Sr_No = @iIndexCol)
BEGIN
SELECT Sr_No FROM #Temp_Table_Col WHERE Sr_No = @iIndexCol
END

my questions
i want to check the record exists or not in the table then execute the query
through following syntex but it gives an error

SET @SQLQuery = N'SELECT ' + CHAR(39) +@TableName + CHAR(39) + ' AS TableName,* FROM [' + @TableName + '] WITH (NOLOCK) WHERE [' + @ColumnName + ']= ' + CHAR(39) + @Value + CHAR(39)
if EXISTS(SP_EXECUTESQL @SQLQuery)
BEGIN EXEC SP_EXECUTESQL @SQLQuery
END

need your assistances

thanks in advance

Patel Mohamad

Post #1064069

Dave Ballantyne

Posted Tuesday, February 15, 2011 1:42 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

Rather than guess, what error does it give ?

Clear Sky SQL
My Blog
Kent user group

Post #1064071

patelmohamad

Posted Tuesday, February 15, 2011 1:45 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:18 AM
Points: 52, Visits: 685

Server: Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near 'SP_EXECUTESQL'.

Patel Mohamad

Post #1064073

Dave Ballantyne

Posted Tuesday, February 15, 2011 1:53 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

I suspect this is a homework question , is it ?

You will have to insert the results of the sp_executesql into a temp table and test against that.

Clear Sky SQL
My Blog
Kent user group

Post #1064077

patelmohamad

Posted Tuesday, February 15, 2011 2:04 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:18 AM
Points: 52, Visits: 685

the following code illustrate you in breif actual i want the table name, respective column data from over all Tables of the Database

DECLARE @iIndexRow INT ,@RowsCount_Row int , @SQLQuery NVARCHAR(4000)
DECLARE @Value VARCHAR(100)
DECLARE @TableName VARCHAR(100) ,@ColumnName VARCHAR(100)
DECLARE @xtype int
DECLARE @ID_Row INT
DECLARE @iIndexCol INT ,@RowsCount_Col int

SELECT @Value = 'VALUE'

SELECT Identity(INT,1,1) AS Sr_No, id, Name INTO #Temp_Table_Row
FROM SYSOBJECTS
WHERE xtype = 'U' ORDER BY [Name]
SELECT @RowsCount_Row = @@RowCount
SELECT @iIndexRow = 1
WHILE @iIndexRow <= @RowsCount_Row
BEGIN --- 1.0
if EXISTS(SELECT Sr_No FROM #Temp_Table_Row WHERE Sr_No = @iIndexRow)
BEGIN -- 1.1
SELECT @ID_Row = ID FROM #Temp_Table_Row WHERE Sr_No = @iIndexRow
SELECT Identity(INT,1,1) AS Sr_No , id, Name , xtype INTO #Temp_Table_Col
FROM Syscolumns
where id = @ID_Row
and xtype in (35 ,98 ,165 ,167 ,173 ,175 ,231 ,231 ,239) -- ,99 For datatypes char varhcar etc
ORDER BY [Name]

SELECT @RowsCount_Col = @@RowCount
SELECT @iIndexCol = 1
WHILE @iIndexCol <= @RowsCount_Col
BEGIN --- 2.0
if EXISTS(SELECT Sr_No FROM #Temp_Table_Col WHERE Sr_No = @iIndexCol)
BEGIN --- 2.1
SELECT @TableName = Object_Name(ID) ,@ColumnName = Name , @xtype = xtype FROM #Temp_Table_Col WHERE Sr_No = @iIndexCol
SET @SQLQuery = N'SELECT ' + CHAR(39) +@TableName + CHAR(39) + ' AS TableName,* FROM [' + @TableName + '] WITH (NOLOCK) WHERE [' + @ColumnName + '] like% ' + CHAR(39) + @Value + CHAR(39)
if EXISTS(SP_EXECUTESQL @SQLQuery)
BEGIN --- 2.1.0
EXEC SP_EXECUTESQL @SQLQuery
END --- 2.1.0
END --- 2.1
SELECT @iIndexCol = @iIndexCol + 1
END --- 2.0
DROP TABLE #Temp_Table_Col

END -- 1.1
SELECT @iIndexRow = @iIndexRow + 1
END --- 1.0
DROP TABLE #Temp_Table_Row

Patel Mohamad

Post #1064086

Shanalise

Posted Friday, February 18, 2011 12:57 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, July 05, 2011 6:02 AM
Points: 2,242, Visits: 208

A bit complicated code, what are you trying to do?

why do you try running a function from a stored procedure after passing a value form the Exist statement

Post #1066173

patelmohamad

Posted Friday, February 18, 2011 3:59 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:18 AM
Points: 52, Visits: 685

this is the normal query
through this we will come to know whiether the records exists or not in the table
if EXISTS(SELECT Sr_No FROM #Temp_Table_Col WHERE Sr_No = @iIndexCol)
BEGIN
SELECT Sr_No FROM #Temp_Table_Col WHERE Sr_No = @iIndexCol
END

my questions
i want to check the record exists or not in the table then execute the query
through following syntax but it gives an error

SET @SQLQuery = N'SELECT ' + CHAR(39) +@TableName + CHAR(39) + ' AS TableName,* FROM [' + @TableName + '] WITH (NOLOCK) WHERE [' + @ColumnName + ']= ' + CHAR(39) + @Value + CHAR(39)
if EXISTS(SP_EXECUTESQL @SQLQuery)
BEGIN EXEC SP_EXECUTESQL @SQLQuery
END
i am assign the query into one variable and then executing it through EXEC SP_EXECUTESQL but before executing the query i want to check the record exists for the build up query

hope you got it.....

Patel Mohamad

Post #1066249

neeraj.it

Posted Saturday, March 03, 2012 2:53 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 25, 2013 10:48 PM
Points: 10, Visits: 25

Try this way..

DECLARE @CNT2 VARCHAR(100)

SET @SQLQuery = N'SELECT @CNT = COUNT(*) FROM [' + @TableName + '] WITH (NOLOCK) WHERE [' + @ColumnName + ']= ' + CHAR(39) + @Value + CHAR(39)

SP_EXECUTESQL @SQLQuery, N'@CNT INT OUT', @CNT2 OUT

if EXISTS(@CNT2>0)
BEGIN
SET @SQLQuery = N'SELECT ' + CHAR(39) +@TableName + CHAR(39) + ' AS TableName,* FROM [' + @TableName + '] WITH (NOLOCK) WHERE [' + @ColumnName + ']= ' + CHAR(39) + @Value + CHAR(39)

EXEC SP_EXECUTESQL @SQLQuery
END

Post #1261112

« Prev Topic | Next Topic »

Permissions