Seeking a general query stored procedure

robinlit

Want to write a general query stored procedure

1. The table name is uncertain
2. The field name is uncertain
3. The field type is uncertain
4. The number of fields is uncertain

At least the first 3 conditions must be fulfilled
SQL roughly describes:

SelectID = 1
SelectName = "ClassID" or SelectName = "name" or SelectName = "ClassID,name"
SelectTable = "Table"

Select'+SelectName+' From'+SelectTable+' Where ID='+SelectID +'

robinlit

Add a demand
5. Uncertain query conditions

SelectName = "ClassID" or SelectName = "name" or SelectName = "ClassID,name"
SelectTable = "Table"
SelectStr = "ID=1"
Select'+SelectName+' From'+SelectTable+' Where'+SelectStr+'

cv4bf5d65fd

Should add one more
6. The displayed column is also uncertain

lone_lau

create proc xx
@f1 varchar(50),
@f2 varchar(50),
@f3 varchar(50),
@tb varchar(50)
as
exec('select'+@f1+','+@f2+','+@f3+' from'+@tb)

seaeva

/*
Function description: General paging display query
    Condition: There is no field indicating increment in the table
Input parameters:
    @tblName: table name
    @strGetFields: need to return the column'*': return all column information
    @fldName: sorted field name
    @OrderType: Set the sort type, non-zero value is descending
    @PageSize: page size
    @PageIndex: page number
    @doCount: Returns the total number of records, non-zero value is returned
    @strOrderBy: Default sort field information (Note: Do not add ORDER BY)
        Format: Field1 DESC, Field2 ASC,
    @strWhere: query conditions (note: do not add WHERE)
Output parameters: @RecordCount: total number of records
Author: ningfeiyang
Created: 2015-01-19
Change record:
*/
ALTER PROCEDURE Pagination2
(
  @tblName varchar(255),
  @strGetFields varchar(1000) ='*',
  @fldName varchar(255) ='',
  @PageSize int = 10,
  @PageIndex int = 1,
  @doCount bit = 0,
  @OrderType bit = 0,
  @strOrderBy varchar(500) ='',
  @strWhere varchar(1500) ='',
  @RecordCount int output
)
AS
DECLARE @strSQL varchar(5000) - main statement
DECLARE @strTmp varchar(200) - Temporary variable
DECLARE @strOrder varchar(400) - sort variable

--If @doCount is not 0, perform total statistics
IF (@doCount != 0)
BEGIN
  DECLARE @sWhere varchar(2000)
  
  SET @sWhere =''
  IF (@strWhere !='')
   SET @sWhere = 'WHERE' + @strWhere
   
  SET @strSQL ='if exists (select * from dbo.sysobjects where id = object_id(``[dbo].[tmpTable]'') and OBJECTPROPERTY(id,''IsUserTable'') = 1) '
  SET @strSQL = @strSQL + 'UPDATE tmpTable SET Total = (SELECT COUNT(*) FROM ['+ @tblName +']' + @sWhere +') '
  SET @strSQL = @strSQL + 'ELSE SELECT COUNT(*) AS Total INTO tmpTable FROM ['+ @tblName +']' + @sWhere
   
  EXEC (@strSQL)
  
  SELECT @RecordCount=Total FROM tmpTable
  
  -Delete the total statistics temporary table
  EXEC ('DROP TABLE tmpTable')
END

--PRINT @RecordCount

--If @OrderType is not 0, perform descending order
IF (@OrderType != 0)
BEGIN
  SET @strTmp = '<(SELECT MIN'
  SET @strOrder = 'ORDER BY' + @strOrderBy +'[' + @fldName +'] DESC'
END
ELSE
BEGIN
  SET @strTmp = '> (SELECT MAX'
  SET @strOrder = 'ORDER BY' + @strOrderBy +'[' + @fldName +'] ASC'
END

--If it is the first page, execute the above code, which will speed up execution
IF @PageIndex = 1
BEGIN
  IF @strWhere !=''
   SET @strSQL ='SELECT TOP '+ str(@PageSize) +' '+ @strGetFields +' FROM ['+ @tblName +'] WHERE '+ @strWhere +' '+ @strOrder
  ELSE
   SET @strSQL ='SELECT TOP '+ str(@PageSize) +' '+ @strGetFields +' FROM ['+ @tblName +']'+ @strOrder
END
ELSE
BEGIN
  --Create automatic number for search table and save to temporary table
  SET @strSQL ='SELECT TOP '+ str(@PageIndex*@PageSize) +' IDENTITY(int,1,1) AS IID, '+ @strGetFields +' INTO #tmpTable FROM ['+ @tblName +']'
  IF @strWhere !=''
   SET @strSQL = @strSQL + 'WHERE' + @strWhere + '' + @strOrder
  ELSE
   SET @strSQL = @strSQL + @strOrder

  --The following code gives @strSQL the SQL code that is actually executed
  SET @strSQL = @strSQL + 'SELECT TOP' + str(@PageSize) + '' + @strGetFields + 'FROM #tmpTable'
     + 'WHERE IID' + @strTmp +'(IID) FROM (SELECT TOP '+ str((@PageIndex-1)*@PageSize) +' IID FROM #tmpTable) AS tblTmp) DROP TABLE #tmpTable'
END

--PRINT @strSQL

--Perform paging query
EXEC (@strSQL)

hhuaixiaozi

USE tempdb
GO

IF OBJECT_ID('dbo.p_Qry') IS NOT NULL
DROP PROC p_Qry
GO

CREATE PROC dbo.p_Qry
@TableName sysname, - the name of the queried table
@FieldList varchar(8000) - List of field names of query results
AS
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#qrywhere') IS NULL
BEGIN
RAISERROR('must define #qrywhere use script:
CREATE TABLE #qrywhere(FieldName sysname, value sql_variant)', 16, 1)
RETURN
END
DECLARE @s varchar(8000)
SET @s =''
SELECT @s = @s + N'OR '+ QUOTENAME(FieldName)
+'=(SELECT value FROM #qrywhere WHERE FieldName=N' + QUOTENAME(FieldName,'''') +')'
FROM #qrywhere
IF @@ROWCOUNT> 0
SET @s = STUFF(@s, 1, 3, 'WHERE')
EXEC('SELECT '+ @FieldList +' FROM '+ @TableName + @s)
GO

-- transfer
CREATE TABLE #qrywhere(FieldName sysname, value sql_variant)
EXEC dbo.p_Qry'sysobjects','*'

INSERT #qrywhere VALUES('xtype','S')
INSERT #qrywhere VALUES('status', 0)
EXEC dbo.p_Qry'sysobjects','*'
GO

- Delete test
DROP PROC dbo.p_Qry
DROP TABLE #qrywhere