none
Problem with STORE PROCEDURE, There is already an object named, "GetCustomersPageWise" in the database

    Question

  • I am having a problem with my SQL statement. I am using a store procedure in a C# ASPX page. How do i get rid of this error? 

    CREATE PROCEDURE GetCustomersPageWise 
    @PageIndex INT = 1 ,@PageSize INT = 10 ,@RecordCount INT OUTPUT AS BEGIN 
    ;WITH cte AS (SELECT DISTINCT TOP 1000 [device_id],[ipaddr],[mtime], ROW_NUMBER() 
    OVER(PARTITION BY [device_id],[ipaddr],[mtime] 
    ORDER BY [device_id] ASC) AS Ranked FROM [RCA_CORE].[dbo].[HDeviceConfig])
    SELECT [device_id],[ipaddr],[mtime] FROM cte WHERE Ranked = 1 END 
    SELECT * FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1 
    DROP TABLE #Results DROP PROCEDURE GetCustomersPageWise

    There is already an object named 'GetCustomersPageWise' in the database. 
      Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
    
     Exception Details: System.Data.SqlClient.SqlException: There is already an object named 'GetCustomersPageWise' in the database.
    
    Source Error: 
    
    
    
    Line 20: 			SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
    Line 21:             DataTable dt = new DataTable();
    Line 22:             sqlDa.Fill(dt);
    Line 23:             if (dt.Rows.Count > 0)
    Line 24:             {
      
    
     Source File:  c:\inetpub\wwwroot\database.aspx    Line:  22 


    • Edited by Chris480602 Friday, February 17, 2017 5:39 PM
    Friday, February 17, 2017 5:38 PM

All replies

  • Try adding stored procedure existence check before creating one.

    IF EXISTS ( select * from sys.procedures where name='<SP NAME>')

     BEGIN 
                        DROP PROC <SP NAME>
    END 

    GO

     --- < Write your code>----


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Sarat (SS) Friday, February 17, 2017 7:01 PM fixed syntax errors
    Friday, February 17, 2017 5:41 PM
  • Modified. Now I have a different error. 

    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'BEGIN'.
    Msg 156, Level 15, State 1, Line 9
    Incorrect syntax near the keyword 'ELSE'.
    Msg 111, Level 15, State 1, Line 16
    'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

    IF EXISTS ( select * from sys.procedures where name='GetCustomersPageWise'
    
    BEGIN 
    
    DROP PROC GetCustomersPageWise
    
    END 
    
    ELSE 
    
    CREATE PROCEDURE GetCustomersPageWise 
    @PageIndex INT = 1 ,@PageSize INT = 10 ,@RecordCount INT OUTPUT AS BEGIN 
    ;WITH cte AS (SELECT DISTINCT TOP 1000 [device_id],[ipaddr],[mtime], ROW_NUMBER() 
    OVER(PARTITION BY [device_id],[ipaddr],[mtime] 
    ORDER BY [device_id] ASC) AS Ranked FROM [RCA_CORE].[dbo].[HDeviceConfig])
    	SELECT [device_id],[ipaddr],[mtime] FROM cte WHERE Ranked = 1 END 
    	SELECT * FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1 
    DROP TABLE #Results DROP PROCEDURE GetCustomersPageWise

    Friday, February 17, 2017 5:51 PM
  • If you need a stored procedure, why not create it once in a database? Why do you need to create it from the application - it makes very little sense.

    Also, the procedure refers to #Results table but it doesn't place the select from cte into that #Results table (that's the intent, I think).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, February 17, 2017 6:02 PM
    Moderator
  • This is tied to a select menu and Paginate Links. 

    <%@ Page Language="C#" Debug="true" %>
    <%@ Import Namespace="System.Data.SqlClient" %>
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Configuration" %>
    <%@ Import Namespace="System.Xml" %>
    <%@ Import Namespace="System.Web.UI.WebControls" %>
    <%@ Import Namespace="System.Web.UI" %>
    <%@ Import Namespace="System.Web" %>
    <%@ Import Namespace="System.Web" %>
    <%@ Import Namespace="System.Collections.Generic" %> 
    <%@ Import Namespace="System" %>
    <script runat="server">
    		private void GetData()
    		{
    			
    			SqlConnection connection = new SqlConnection("Server=PHXLIT3986\\RADIADB01052017B;Database=RCA_CORE;User Id=sa;Password=password;");
    			connection.Open();
    			SqlCommand sqlCmd = new SqlCommand
    			("CREATE PROCEDURE GetCustomersPageWise @PageIndex INT = 1 ,@PageSize INT = 10 ,@RecordCount INT OUTPUT AS BEGIN ;WITH cte AS (SELECT DISTINCT TOP 1000 [device_id],[ipaddr],[mtime], ROW_NUMBER() OVER(PARTITION BY [device_id],[ipaddr],[mtime] ORDER BY [device_id] ASC) AS Ranked FROM [RCA_CORE].[dbo].[HDeviceConfig])SELECT [device_id],[ipaddr],[mtime] FROM cte WHERE Ranked = 1 END SELECT * FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1 DROP TABLE #Results DROP PROCEDURE GetCustomersPageWise", connection);
    			SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
                DataTable dt = new DataTable();
                sqlDa.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
                connection.Close();
            }
    
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!Page.IsPostBack)
                    GetData();
            }
    		
    		protected void PageSize_Changed(object sender, EventArgs e)
    		{
    			this.GetCustomersPageWise(1);
    		}
    		
    		private void PopulatePager(int recordCount, int currentPage)
    		{
    			double dblPageCount = (double)((decimal)recordCount / decimal.Parse(ddlPageSize.SelectedValue));
    			int pageCount = (int)Math.Ceiling(dblPageCount);
    			List<ListItem> pages = new List<ListItem>();
    			if (pageCount > 0)
    			{
    				pages.Add(new ListItem("First", "1", currentPage > 1));
    				for (int i = 1; i <= pageCount; i++)
    				{
    					pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
    				}
    				pages.Add(new ListItem("Last", pageCount.ToString(), currentPage < pageCount));
    			}
    			rptPager.DataSource = pages;
    			rptPager.DataBind();
    		}
    		
    		private void GetCustomersPageWise(int pageIndex)
    		{
    			string constring = "PHXLIT3986\\RADIADB01052017B;Database=RCA_CORE;User Id=sa;Password=password;";
    			string queryString = "CREATE PROCEDURE GetCustomersPageWise11 @PageIndex INT = 1 ,@PageSize INT = 10 ,@RecordCount INT OUTPUT AS BEGIN ;WITH cte AS (SELECT DISTINCT TOP 1000 [device_id],[ipaddr],[mtime], ROW_NUMBER() OVER(PARTITION BY [device_id],[ipaddr],[mtime] ORDER BY [device_id] ASC) AS Ranked FROM [RCA_CORE].[dbo].[HDeviceConfig])SELECT [device_id],[ipaddr],[mtime] FROM cte WHERE Ranked = 1 END SELECT * FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1 DROP TABLE #Results DROP PROCEDURE GetCustomersPageWise";
    
    			using (SqlConnection con = new SqlConnection(constring))
    			{
    				using (SqlCommand sqlCmd = new SqlCommand("GetCustomersPageWise", con))
    					{
    						sqlCmd.CommandType = CommandType.StoredProcedure;
    						sqlCmd.Parameters.AddWithValue("@PageIndex", pageIndex);
    						sqlCmd.Parameters.AddWithValue("@PageSize", int.Parse(ddlPageSize.SelectedValue));
    						sqlCmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
    						sqlCmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
    						con.Open();
    						IDataReader idr = sqlCmd.ExecuteReader();
    						GridView1.DataSource = idr;
    						GridView1.DataBind();
    						idr.Close();
    						con.Close();
    						int recordCount = Convert.ToInt32(sqlCmd.Parameters["@RecordCount"].Value);
    						this.PopulatePager(recordCount, pageIndex);
    					}
    			}
    		}
    		
    		protected void Page_Changed(object sender, EventArgs e)
    		{
    			int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
    			this.GetCustomersPageWise(pageIndex);
    		}
    	
    </script>
    <html>
    	<body>
    	<form runat="server" method="post">
    	<table cellpadding="0" cellspacing="0">
    		<tr>
    			<td width="350px">&nbsp;</td>
    			<td>
    				PageSize:
    				<asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PageSize_Changed">
    					<asp:ListItem Text="10" Value="10" />
    					<asp:ListItem Text="25" Value="25" />
    					<asp:ListItem Text="50" Value="50" />
    				</asp:DropDownList>
    			</td>
    		</tr>
    	</table>
    	<br /><br />
    	<table cellpadding="0" cellspacing="0">
    		<tr>
    			<td width="350px">&nbsp;</td>
    			<td>
    				<asp:Repeater ID="rptPager" runat="server">
    					<ItemTemplate>
    						<asp:LinkButton ID="lnkPage" runat="server" Text = '<%#Eval("Text") %>' CommandArgument = '<%# Eval("Value") %>' Enabled = '<%# Eval("Enabled") %>' OnClick = "Page_Changed"></asp:LinkButton>
    					</ItemTemplate>
    				</asp:Repeater>
    			</td>
    		</tr>
    	</table>
    	<br /><br />
    	<table cellpadding="0" cellspacing="0">
    		<tr>
    			<td valign=top>	
    				<div runat="server">
    					<!-- #Include virtual="/LastLogon.html" -->
    				</div>  
    					
    			</td>
    			<td width=100px>&nbsp;</td>
    			<td valign=top>
    				<br /><br />
    				 <asp:gridview id="GridView1" 
    					autogeneratecolumns="true" 
    					runat="server">
    				  </asp:gridview>
    				
    				<br /><br />
    			<td width=50px>&nbsp;</td>
    		</tr>
    	</table>
    	</form>
    </body>
    </html>


    Friday, February 17, 2017 6:16 PM
  • Do you have a DBA in your organization? Are you a developer?

    In the database you don't create temporary stored procedures, normally. You add the stored procedure to the database once and re-use it as needed.

    So, if you need a stored procedure to perform a task for your application, contact the DBA of the organization, provide the script of the procedure and ask help of adding this procedure to the database.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, February 17, 2017 6:30 PM
    Moderator
  • Nope. I just want this working on my test database, for reporting reasons. Not looking to involve a DBA or developer. 

    Anyone else have any ideas?

    Friday, February 17, 2017 6:48 PM
  • In this case just add the stored procedure to your test DB and be done with it.

    If you insist on creating the stored procedure in the code, you would have to utilize a dynamic SQL, e.g.

    IF EXISTS ( select * from sys.procedures where name='GetCustomersPageWise'
    
    -- Do nothing - procedure already exists
    ELSE
    begin
       declare @SQLCode nvarchar(max)
    
      set @SQLCode = 'CREATE PROCEDURE .... ' -- full code of the procedure here with single quote converted to double single quote
    
    
       execute (@SQLCode)
    end
    
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, February 17, 2017 6:57 PM
    Moderator
  • Or try:

    IF NOT EXISTS ( select * from sys.procedures where name='GetCustomersPageWise'
    
       declare @SQLCode nvarchar(max)
    
      set @SQLCode = 'CREATE PROCEDURE .... ' -- full code of the procedure here with single quote converted to double single quote
    
    
       execute (@SQLCode)
    end
    

    Friday, February 17, 2017 7:04 PM
  • Good point :)

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, February 17, 2017 7:33 PM
    Moderator
  • I took a look at your stored procedure GetCustomersPageWise. In the CTE, you should not use TOP 1000, otherwise you will never get the records after top 1000.

    By the way, in your other post "Distinct Statement query", if the solutions answer your question, you should mark it as an Answer so that it may help others who have the same problem.


    A Fan of SSIS, SSRS and SSAS

    Friday, February 17, 2017 8:07 PM
  • Yes, the code in the procedure makes very little sense as well as the whole code posted in this thread for ASP.NET page.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, February 17, 2017 8:10 PM
    Moderator
  • Modified. Now I have a different error. 

    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'BEGIN'.
    Msg 156, Level 15, State 1, Line 9
    Incorrect syntax near the keyword 'ELSE'.
    Msg 111, Level 15, State 1, Line 16
    'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

    IF EXISTS ( select * from sys.procedures where name='GetCustomersPageWise' <= Missing closing ) BEGIN DROP PROC GetCustomersPageWise END ELSE <= Remove it

    GO <= Add batch separator; the CREATE PROCEDURE command must be first in separate batch!

    CREATE PROCEDURE GetCustomersPageWise


    See comments in SQL statement above, like <= Missing closing )


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Saturday, February 18, 2017 6:29 AM
  • the GO seperator is breaking the code. Any ideas? 

    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON DECLARE @PageIndex int DECLARE @PageSize int IF EXISTS
      (SELECT *
       FROM sys.procedures
       WHERE name='GetCustomersPageWise') BEGIN
    DROP PROCEDURE GetCustomersPageWise END GO
    CREATE PROCEDURE GetCustomersPageWise @PageIndex INT = 1,
                                                           @PageSize INT = 10,
                                                                           @RecordCount INT OUTPUT AS BEGIN;
    
    WITH cte AS
      (SELECT DISTINCT TOP 500 [device_id],
                           [ipaddr],
                           [mtime],
                           ROW_NUMBER()OVER(PARTITION BY [device_id],[ipaddr],[mtime]
                                            ORDER BY [device_id] ASC) AS Ranked
       FROM [RCA_CORE].[dbo].[HDeviceConfig])
    SELECT [device_id],
           [ipaddr],
           [mtime]
    FROM cte
    WHERE Ranked = 1 END
      SELECT *
      FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
      DROP TABLE #Results
      DROP PROCEDURE GetCustomersPageWise END

    Incorrect syntax near 'GO'.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'GO'.

    <%@ Page Language="C#" Debug="true" %>
    <%@ Import Namespace="System.Data.SqlClient" %>
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Configuration" %>
    <%@ Import Namespace="System.Xml" %>
    <%@ Import Namespace="System.Web.UI.WebControls" %>
    <%@ Import Namespace="System.Web.UI" %>
    <%@ Import Namespace="System.Web" %>
    <%@ Import Namespace="System.Web" %>
    <%@ Import Namespace="System.Collections.Generic" %> 
    <%@ Import Namespace="System" %>
    <script runat="server">
    		private void GetData()
    		{
    			
    			SqlConnection connection = new SqlConnection("Server=PHXLIT3986\\RADIADB01052017B;Database=RCA_CORE;User Id=sa;Password=password;");
    			connection.Open();
    			SqlCommand sqlCmd = new SqlCommand
    			("SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON DECLARE @PageIndex int DECLARE @PageSize int IF EXISTS ( select * from sys.procedures where name='GetCustomersPageWise') BEGIN DROP PROCEDURE GetCustomersPageWise END GO CREATE PROCEDURE GetCustomersPageWise @PageIndex INT = 1 ,@PageSize INT = 10 ,@RecordCount INT OUTPUT AS BEGIN;WITH cte AS (SELECT DISTINCT TOP 500 [device_id],[ipaddr],[mtime], ROW_NUMBER()OVER(PARTITION BY [device_id],[ipaddr],[mtime] ORDER BY [device_id] ASC) AS Ranked FROM [RCA_CORE].[dbo].[HDeviceConfig])SELECT [device_id],[ipaddr],[mtime] FROM cte WHERE Ranked = 1 END SELECT * FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1 DROP TABLE #Results DROP PROCEDURE GetCustomersPageWise END", connection);
    			SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
                DataTable dt = new DataTable();
                sqlDa.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
                connection.Close();
            }
    
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!Page.IsPostBack)
                    GetData();
            }
    		
    		protected void PageSize_Changed(object sender, EventArgs e)
    		{
    			this.GetCustomersPageWise(1);
    		}
    		
    		private void PopulatePager(int recordCount, int currentPage)
    		{
    			double dblPageCount = (double)((decimal)recordCount / decimal.Parse(ddlPageSize.SelectedValue));
    			int pageCount = (int)Math.Ceiling(dblPageCount);
    			List<ListItem> pages = new List<ListItem>();
    			if (pageCount > 0)
    			{
    				pages.Add(new ListItem("First", "1", currentPage > 1));
    				for (int i = 1; i <= pageCount; i++)
    				{
    					pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
    				}
    				pages.Add(new ListItem("Last", pageCount.ToString(), currentPage < pageCount));
    			}
    			rptPager.DataSource = pages;
    			rptPager.DataBind();
    		}
    		
    		private void GetCustomersPageWise(int pageIndex)
    		{
    			string constring = "PHXLIT3986\\RADIADB01052017B;Database=RCA_CORE;User Id=sa;Password=password;";
    			string queryString = "CREATE PROCEDURE GetCustomersPageWise11 @PageIndex INT = 1 ,@PageSize INT = 10 ,@RecordCount INT OUTPUT AS BEGIN ;WITH cte AS (SELECT DISTINCT TOP 1000 [device_id],[ipaddr],[mtime], ROW_NUMBER() OVER(PARTITION BY [device_id],[ipaddr],[mtime] ORDER BY [device_id] ASC) AS Ranked FROM [RCA_CORE].[dbo].[HDeviceConfig])SELECT [device_id],[ipaddr],[mtime] FROM cte WHERE Ranked = 1 END SELECT * FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1 DROP TABLE #Results DROP PROCEDURE GetCustomersPageWise";
    
    			using (SqlConnection con = new SqlConnection(constring))
    			{
    				using (SqlCommand sqlCmd = new SqlCommand("GetCustomersPageWise", con))
    					{
    						sqlCmd.CommandType = CommandType.StoredProcedure;
    						sqlCmd.Parameters.AddWithValue("@PageIndex", pageIndex);
    						sqlCmd.Parameters.AddWithValue("@PageSize", int.Parse(ddlPageSize.SelectedValue));
    						sqlCmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
    						sqlCmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
    						con.Open();
    						IDataReader idr = sqlCmd.ExecuteReader();
    						GridView1.DataSource = idr;
    						GridView1.DataBind();
    						idr.Close();
    						con.Close();
    						int recordCount = Convert.ToInt32(sqlCmd.Parameters["@RecordCount"].Value);
    						this.PopulatePager(recordCount, pageIndex);
    					}
    			}
    		}
    		
    		protected void Page_Changed(object sender, EventArgs e)
    		{
    			int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
    			this.GetCustomersPageWise(pageIndex);
    		}
    	
    </script>
    <html>
    	<body>
    	<form runat="server" method="post">
    	<table cellpadding="0" cellspacing="0">
    		<tr>
    			<td width="350px">&nbsp;</td>
    			<td>
    				PageSize:
    				<asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PageSize_Changed">
    					<asp:ListItem Text="10" Value="10" />
    					<asp:ListItem Text="25" Value="25" />
    					<asp:ListItem Text="50" Value="50" />
    				</asp:DropDownList>
    			</td>
    		</tr>
    	</table>
    	<br /><br />
    	<table cellpadding="0" cellspacing="0">
    		<tr>
    			<td width="350px">&nbsp;</td>
    			<td>
    				<asp:Repeater ID="rptPager" runat="server">
    					<ItemTemplate>
    						<asp:LinkButton ID="lnkPage" runat="server" Text = '<%#Eval("Text") %>' CommandArgument = '<%# Eval("Value") %>' Enabled = '<%# Eval("Enabled") %>' OnClick = "Page_Changed"></asp:LinkButton>
    					</ItemTemplate>
    				</asp:Repeater>
    			</td>
    		</tr>
    	</table>
    	<br /><br />
    	<table cellpadding="0" cellspacing="0">
    		<tr>
    			<td valign=top>	
    				<div runat="server">
    					<!-- #Include virtual="/LastLogon.html" -->
    				</div>  
    					
    			</td>
    			<td width=100px>&nbsp;</td>
    			<td valign=top>
    				<br /><br />
    				 <asp:gridview id="GridView1" 
    					autogeneratecolumns="true" 
    					runat="server">
    				  </asp:gridview>
    				
    				<br /><br />
    			<td width=50px>&nbsp;</td>
    		</tr>
    	</table>
    	</form>
    </body>
    </html>

    Sunday, February 19, 2017 6:35 AM
  • GO is a batch separator in SSMS. It can not be used from the script executed by the application. Did you see all our comments? If you need to create the procedure using code, the only way to do so after that check is to use dynamic SQL as I've shown.

    Also the procedure itself has several flaws.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, February 19, 2017 10:15 PM
    Moderator
  • If you necessarily have to create the SP on the fly, try adding IF EXISTS at the beginning

    IF EXISTS (select * from sys.procedures where name='GetCustomersPageWise'
    BEGIN 
    DROP PROC GetCustomersPageWise
    END 

    If you can run the CREATE PROC.. statement once during database setup, you can also try
    replacing CREATE PROC by ALTER PROC.


    pcofre


    • Edited by pcofreMVP Monday, February 20, 2017 3:19 AM
    Monday, February 20, 2017 3:19 AM
  • To paginate the recordset, here is the example:

    -- Fixed record number per page
    ALTER PROCEDURE [dbo].[PaginateDataset]
    	@PageNumber int = 1
    AS
    BEGIN
    	SET NOCOUNT ON;
    	
    	SELECT TOP 10 *
    	FROM (
    		SELECT *, ROW_NUMBER() OVER (ORDER BY PK_Column) AS RowNumber
    		FROM YourTable
    	) AS t
    	WHERE RowNumber >= (@PageNumber - 1) * 10 + 1
    	AND RowNumber <= @PageNumber * 10
    END
    GO

    Or

    -- Dynamic record number per page
    ALTER PROCEDURE [dbo].[PaginateDataset]
    	@PageNumber int = 1,
    	@RowNumberPerPage int = 10
    AS
    BEGIN
    	SET NOCOUNT ON;
    	
    	DECLARE @sql nvarchar(max);
    	
    	SET @sql = '
    		SELECT TOP ' + CONVERT(varchar(8), @RowNumberPerPage) + ' *
    		FROM (
    			SELECT *, ROW_NUMBER() OVER (ORDER BY PK_Column) AS RowNumber
    			FROM YourTable
    		) AS t
    		WHERE RowNumber >= (' + CONVERT(varchar(8), @PageNumber) + ' - 1) * ' + CONVERT(varchar(8), @RowNumberPerPage) + ' + 1
    		AND RowNumber <= ' + CONVERT(varchar(8), @PageNumber) + ' * ' + CONVERT(varchar(8), @RowNumberPerPage) + '
    	'
    	--PRINT @sql;
    	EXECUTE sp_executesql @sql;
    END
    GO


    A Fan of SSIS, SSRS and SSAS

    Monday, February 20, 2017 2:12 PM
  • I see no need for dynamic SQL in the procedure.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, February 20, 2017 4:30 PM
    Moderator
  • I see no need for dynamic SQL in the procedure.

    Yeah, you are right. for his stored procedure, the first one is okay. I just posted the other one in case someone needs to change the record number per page.

    A Fan of SSIS, SSRS and SSAS

    Monday, February 20, 2017 5:03 PM
  • But even in this case you don't need a dynamic SQL, that was my point. I believe starting with SQL 2005 you can use variable for the top clause.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Monday, February 20, 2017 5:37 PM
    Moderator
  • You are right. The variable must be in the parentheses:

    CREATE PROCEDURE [dbo].[PaginateDataset]
    	@PageNumber int = 1,
    	@RowNumberPerPage int = 10
    AS
    BEGIN
    	SET NOCOUNT ON;
    	
    	SELECT TOP (@RowNumberPerPage) *
    	FROM (
    		SELECT *, ROW_NUMBER() OVER (ORDER BY PK_Column) AS RowNumber
    		FROM YourTable
    	) AS t
    	WHERE RowNumber >= (@PageNumber - 1) * @RowNumberPerPage + 1
    	AND RowNumber <= @PageNumber * @RowNumberPerPage
    END
    GO
    Thanks.


    A Fan of SSIS, SSRS and SSAS


    • Edited by Guoxiong Monday, February 20, 2017 6:09 PM
    Monday, February 20, 2017 6:08 PM
  • I still dont see how any of that fits within my code. 

    Could you fit your statement into my code? 

    <%@ Page Language="C#" Debug="true" %>
    <%@ Import Namespace="System.Data.SqlClient" %>
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Configuration" %>
    <%@ Import Namespace="System.Xml" %>
    <%@ Import Namespace="System.Web.UI.WebControls" %>
    <%@ Import Namespace="System.Web.UI" %>
    <%@ Import Namespace="System.Web" %>
    <%@ Import Namespace="System.Web" %>
    <%@ Import Namespace="System.Collections.Generic" %> 
    <%@ Import Namespace="System" %>
    <script runat="server">
    		private void GetData()
    		{
    			
    			SqlConnection connection = new SqlConnection("Server=PHXLIT3986\\RADIADB01052017B;Database=RCA_CORE;User Id=sa;Password=password;");
    			connection.Open();
    			SqlCommand sqlCmd = new SqlCommand
    			("SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON DECLARE @PageIndex int DECLARE @PageSize int IF EXISTS ( select * from sys.procedures where name='GetCustomersPageWise') BEGIN DROP PROCEDURE GetCustomersPageWise END GO CREATE PROCEDURE GetCustomersPageWise @PageIndex INT = 1 ,@PageSize INT = 10 ,@RecordCount INT OUTPUT AS BEGIN;WITH cte AS (SELECT DISTINCT TOP 500 [device_id],[ipaddr],[mtime], ROW_NUMBER()OVER(PARTITION BY [device_id],[ipaddr],[mtime] ORDER BY [device_id] ASC) AS Ranked FROM [RCA_CORE].[dbo].[HDeviceConfig])SELECT [device_id],[ipaddr],[mtime] FROM cte WHERE Ranked = 1 END SELECT * FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1 DROP TABLE #Results DROP PROCEDURE GetCustomersPageWise END", connection);
    			SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
                DataTable dt = new DataTable();
                sqlDa.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
                connection.Close();
            }
    
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!Page.IsPostBack)
                    GetData();
            }
    		
    		protected void PageSize_Changed(object sender, EventArgs e)
    		{
    			this.GetCustomersPageWise(1);
    		}
    		
    		private void PopulatePager(int recordCount, int currentPage)
    		{
    			double dblPageCount = (double)((decimal)recordCount / decimal.Parse(ddlPageSize.SelectedValue));
    			int pageCount = (int)Math.Ceiling(dblPageCount);
    			List<ListItem> pages = new List<ListItem>();
    			if (pageCount > 0)
    			{
    				pages.Add(new ListItem("First", "1", currentPage > 1));
    				for (int i = 1; i <= pageCount; i++)
    				{
    					pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
    				}
    				pages.Add(new ListItem("Last", pageCount.ToString(), currentPage < pageCount));
    			}
    			rptPager.DataSource = pages;
    			rptPager.DataBind();
    		}
    		
    		private void GetCustomersPageWise(int pageIndex)
    		{
    			string constring = "PHXLIT3986\\RADIADB01052017B;Database=RCA_CORE;User Id=sa;Password=password;";
    			string queryString = "CREATE PROCEDURE GetCustomersPageWise11 @PageIndex INT = 1 ,@PageSize INT = 10 ,@RecordCount INT OUTPUT AS BEGIN ;WITH cte AS (SELECT DISTINCT TOP 1000 [device_id],[ipaddr],[mtime], ROW_NUMBER() OVER(PARTITION BY [device_id],[ipaddr],[mtime] ORDER BY [device_id] ASC) AS Ranked FROM [RCA_CORE].[dbo].[HDeviceConfig])SELECT [device_id],[ipaddr],[mtime] FROM cte WHERE Ranked = 1 END SELECT * FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1 DROP TABLE #Results DROP PROCEDURE GetCustomersPageWise";
    
    			using (SqlConnection con = new SqlConnection(constring))
    			{
    				using (SqlCommand sqlCmd = new SqlCommand("GetCustomersPageWise", con))
    					{
    						sqlCmd.CommandType = CommandType.StoredProcedure;
    						sqlCmd.Parameters.AddWithValue("@PageIndex", pageIndex);
    						sqlCmd.Parameters.AddWithValue("@PageSize", int.Parse(ddlPageSize.SelectedValue));
    						sqlCmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
    						sqlCmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
    						con.Open();
    						IDataReader idr = sqlCmd.ExecuteReader();
    						GridView1.DataSource = idr;
    						GridView1.DataBind();
    						idr.Close();
    						con.Close();
    						int recordCount = Convert.ToInt32(sqlCmd.Parameters["@RecordCount"].Value);
    						this.PopulatePager(recordCount, pageIndex);
    					}
    			}
    		}
    		
    		protected void Page_Changed(object sender, EventArgs e)
    		{
    			int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
    			this.GetCustomersPageWise(pageIndex);
    		}
    	
    </script>
    <html>
    	<body>
    	<form runat="server" method="post">
    	<table cellpadding="0" cellspacing="0">
    		<tr>
    			<td width="350px">&nbsp;</td>
    			<td>
    				PageSize:
    				<asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PageSize_Changed">
    					<asp:ListItem Text="10" Value="10" />
    					<asp:ListItem Text="25" Value="25" />
    					<asp:ListItem Text="50" Value="50" />
    				</asp:DropDownList>
    			</td>
    		</tr>
    	</table>
    	<br /><br />
    	<table cellpadding="0" cellspacing="0">
    		<tr>
    			<td width="350px">&nbsp;</td>
    			<td>
    				<asp:Repeater ID="rptPager" runat="server">
    					<ItemTemplate>
    						<asp:LinkButton ID="lnkPage" runat="server" Text = '<%#Eval("Text") %>' CommandArgument = '<%# Eval("Value") %>' Enabled = '<%# Eval("Enabled") %>' OnClick = "Page_Changed"></asp:LinkButton>
    					</ItemTemplate>
    				</asp:Repeater>
    			</td>
    		</tr>
    	</table>
    	<br /><br />
    	<table cellpadding="0" cellspacing="0">
    		<tr>
    			<td valign=top>	
    				<div runat="server">
    					<!-- #Include virtual="/LastLogon.html" -->
    				</div>  
    					
    			</td>
    			<td width=100px>&nbsp;</td>
    			<td valign=top>
    				<br /><br />
    				 <asp:gridview id="GridView1" 
    					autogeneratecolumns="true" 
    					runat="server">
    				  </asp:gridview>
    				
    				<br /><br />
    			<td width=50px>&nbsp;</td>
    		</tr>
    	</table>
    	</form>
    </body>
    </html>

    Monday, February 20, 2017 6:47 PM
  • Do you have SQL Server installed locally? Do you have SSMS installed?

    Can you 

    1. Add a stored procedure to your test database (using SSMS)

    2. Test it with various parameters.

    3. Once the procedure is tested and working as expected, remove creation of the procedure code from your code and just call it from your code.

    4. Once this is done, script that procedure code in SSMS but first go to Tools/Options/SQL Server Object Explorer (it should be right at the bottom) and Scripting Check for Object Existence to true (you may want to re-set it back later). Then script your procedure which you already tested - it should give you the script with the check for existence. Run this script. Then you may use script in your code assuming that script doesn't have GO statements.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, February 20, 2017 8:52 PM
    Moderator
  • I do not know why you are using temporary table #Results which is not created in your script. As Naomi said, you have to create a stored procedure in the database first and then you just need to call that SP (by passing variables @PageIndex and @PageSize) in your aspx file to get your results.

    A Fan of SSIS, SSRS and SSAS

    Monday, February 20, 2017 9:34 PM