November 5, 2008 at 1:27 am
Hello
here my problem
we have to isert a SP record set values int temp tabel....
The SP willl change dynamically
SET @sproc = @strTables // proc name it will change runtime
SET @sproc_params = @sproc_paramsfrom // proc parameter
SET @query = @sproc + ' ' + @sproc_params
i got some code
CREATE TABLE #TEMP1(TaskName varchar(2000))
insert into #TEMP1(TaskName)exec SP_executesql @query
SELECT * FROM #TEMP1
Here creating temp table #temp1
But at the creating time here we specifiyng the field name TaskName varchar(2000)
But in this our requirement. we have to create the temp table the basis of exec SP_executesql @query
That means the recordset(exec SP_executesql @query) fields and datas should be same in the temp table
The coloumn will change.................any time............ we changing the sp name and params aspx page.....................
Harikrishnan.S
November 6, 2008 at 8:40 am
What you appear to need to accomplish is the functional equivalent of the following invalid (my assumption) T-SQL code:
EXEC spWhatever @params
INTO #TEMP1
I remember seeing someone ask this question once before, but I have no idea what heading it was under nor what the topic was, so I don't quite know how to search for it. I also don't recall the solution, but I'm fairly certain there's a workaround. Perhaps someone will recognize this and provide more detail on how to accomplish it...
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 6, 2008 at 9:09 pm
Hello THnks for ur valble commnts
IN ur Case
EXEC SP @param
INTO #TEMP1
I have to chsnge this SP NAme and params will change anytime..............
See my SP
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SELECT_WITH_PAGING_TOP] (
@strFields varchar(4000),
@strPK varchar(100),
@strTables varchar(4000),
@intPageNo int = 1,
@intPageSize int = NULL,
@blnGetRecordCount bit = 0,
@strFilter varchar(8000) = NULL,
@strSort varchar(8000) = NULL,
@strGroup varchar(8000) = NULL,
@isStoredProcedure bit = 0,
@sprocName varchar(4000)=NULL,
@sproc_paramsfrom varchar(4000)=NULL
)
/* Executes a SELECT statement that the parameters define,and returns a particular page of data (or all
rows) efficiently. */
AS
DECLARE @blnBringAllRecords bit
DECLARE @strPageNo varchar(50)
DECLARE @strPageSize varchar(50)
DECLARE @strSkippedRows varchar(50)
DECLARE @strFilterCriteria varchar(8000)
DECLARE @strSimpleFilter varchar(8000)
DECLARE @strSortCriteria varchar(8000)
DECLARE @strGroupCriteria varchar(8000)
DECLARE @intRecordcount int
DECLARE @intPagecount int
DECLARE @isSP bit
Declare @paramss varchar(8000)
Declare @STR varchar(8000)
--
DECLARE @sproc as varchar(4000)
DECLARE @sproc_params as varchar(4000)
DECLARE @query as nvarchar(4000)
-- SET @sproc = @sprocName
-- SET @sproc_params = @sproc_paramsfrom
-- SET @query = @sproc + ' ' + @sproc_params
SET @query = N'TEMP_PROC ' + ' ' + N'@ViewAs=1,@ID=1'
/* Normalize the paging criteria.
If no meaningful inputs are provided, we can avoid paging and execute a more efficient query, so we will
set a flag that will help avoid paging (blnBringAllRecords). */
IF @isStoredProcedure>0
SET @isSP=@isStoredProcedure
IF @intPageNo < 1
SET @intPageNo = 1
SET @strPageNo = CONVERT(varchar(50), @intPageNo)
IF @intPageSize IS NULL OR @intPageSize < 1 -- Bring all records, don't do paging.
SET @blnBringAllRecords = 1
ELSE
BEGIN
SET @blnBringAllRecords = 0
SET @strPageSize = CONVERT(varchar(50), @intPageSize)
SET @strPageNo = CONVERT(varchar(50), @intPageNo)
SET @strSkippedRows = CONVERT(varchar(50), @intPageSize * (@intPageNo - 1))
END
/* Normalize the filter and sorting criteria.
If the criteria are empty, we will avoid filtering and sorting, respectively, by executing more efficient
queries. */
IF @strFilter IS NOT NULL AND @strFilter != ''
BEGIN
SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
SET @strSimpleFilter = ' AND ' + @strFilter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilterCriteria = ''
END
IF @strSort IS NOT NULL AND @strSort != ''
SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
ELSE
SET @strSortCriteria = ''
IF @strGroup IS NOT NULL AND @strGroup != ''
SET @strGroupCriteria = ' GROUP BY ' + @strGroup + ' '
ELSE
SET @strGroupCriteria = ''
/* Now start doing the real work. */
IF @isSP = 0--For checking that the given tblTableNAme is a SP
BEGIN
IF @blnBringAllRecords = 1 -- Ignore paging and run a simple SELECT.
BEGIN
EXEC (
'SELECT ' + @strFields + ' FROM ' + @strTables + @strFilterCriteria +
@strGroupCriteria + @strSortCriteria
)
END -- We had to bring all records.
ELSE -- Bring only a particular page.
BEGIN
IF @intPageNo = 1 -- In this case we can execute a more efficient
-- query with no subqueries.
EXEC (
'SELECT TOP ' + @strPageSize + ' ' + @strFields + ' FROM ' + @strTables +
@strFilterCriteria + @strGroupCriteria + @strSortCriteria
)
ELSE -- Execute a structure of subqueries that brings the correct page.
EXEC (
'SELECT ' + @strFields + ' FROM ' + @strTables + ' WHERE ' + @strPK + ' IN ' + '
(SELECT TOP ' + @strPageSize + ' ' + @strPK + ' FROM ' + @strTables +
' WHERE ' + @strPK + ' NOT IN ' + '
(SELECT TOP ' + @strSkippedRows + ' ' + @strPK + ' FROM ' + @strTables +
@strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') ' +
@strSimpleFilter +
@strGroupCriteria +
@strSortCriteria + ') ' +
@strGroupCriteria +
@strSortCriteria
)
END
END -- We had to bring a particular page.
ELSE-- Run the SP here
begin
DECLARE @SPNAME varchar(1000);
SET @SPNAME =@query ;
select * into #table from openrowset('sqloledb','Provider=sqloledb;UID=sa;
PWD=exalt;Database=TAPMS;Server=192.168.0.200;Trusted_Connection=Yes','SET NOCOUNT ON;
SET FMTONLY OFF;exec '+@SPNAME+')
IF @intPageNo = 1
EXEC (
'SELECT TOP ' + @strPageSize + ' '+ @strFields + ' FROM #table'+
@strFilterCriteria + @strGroupCriteria + @strSortCriteria
)
ELSE -- Execute a structure of subqueries that brings the correct page.
EXEC (
'SELECT ' + @strFields + ' FROM #table WHERE ' + @strPK + ' IN ' + '
(SELECT TOP ' + @strPageSize + ' ' + @strPK + ' FROM #table WHERE ' + @strPK + ' NOT IN ' + '
(SELECT TOP ' + @strSkippedRows + ' ' + @strPK + ' FROM #table' +
@strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') ' +
@strSimpleFilter +
@strGroupCriteria +
@strSortCriteria + ') ' +
@strGroupCriteria +
@strSortCriteria
)
end
Can u solve thsi
Pls
Harikrishnan.S
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply