February 15, 2011 at 1:33 am
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
February 15, 2011 at 1:42 am
Rather than guess, what error does it give ?
February 15, 2011 at 1:45 am
Server: Msg 170, Level 15, State 1, Line 34
Line 34: Incorrect syntax near 'SP_EXECUTESQL'.
Patel Mohamad
February 15, 2011 at 1:53 am
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.
February 15, 2011 at 2:04 am
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
February 18, 2011 at 12:57 am
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
February 18, 2011 at 3:59 am
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
March 3, 2012 at 2:53 am
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
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy