none
Can't create a complex type for my stored procedure results RRS feed

  • Question

  • I have a database with four stored procedure that I'd like to access from an entity model. I followed the directions here and other places just like it:

    http://msdn.microsoft.com/en-us/library/bb896231.aspx

    With only one of my stored procedures, I get to the step where I click on the "Get Column Information" button, but the text box below says "The selected stored procedure returns no columns" even though it does return columns. In fact it returns 62 columns. I can execute the stored procedure and see valid results.

    Why can't I create a Complex Type for the return value of this procedure?

    Thanks,

    Dogulas

    Monday, August 8, 2011 8:37 PM

Answers

  • I see. I just tested it and it seems the designer runs the procedure a little differently than I thought so it seems it would not help.

     

    There is another thing though. I do hope you validate the safety of the stuff you pass to such stored procedure as this can easily be a big security hole. Keep in mind that you are constructing an SQL query using the value passed to the procedure. What if the @p1 is not just a comma separated list of numbers, but

    '1) update tblClient set ClientName =  \'bogus\'; select(1'

     

    Apart from this problem you are also forcing the database server to parse the query and build the execution plan each and every time you call this stored procedure. That's a waste!

    IMnsHO it's better to use something like

    CREATE FUNCTION dbo.SplitList(
      @Options varchar(8000),
      @Delim varchar(5)
    ) RETURNS @tbl TABLE(Id Int PRIMARY KEY)
    as
    BEGIN
    	IF (@Options is not NULL) BEGIN
    	  Declare @idx int, @Id Int;
    	  SET @idx = CHARINDEX( @Delim, @Options)
    		IF (@idx = 1) BEGIN
    	    SET @Options = substring( @Options, @idx + 1, 8000);
    	    SET @idx = CHARINDEX( @Delim, @Options);
    		END
    	  WHILE (@idx > 0) BEGIN
    			SET @Id = substring( @Options, 1, @idx - 1)
    			IF not exists (SELECT * FROM @tbl WHERE Id = @Id)
    		    INSERT INTO @tbl (
    				 Id
    				) VALUES (
    				 @Id
    				);
    	    SET @Options = substring( @Options, @idx + 1, 8000)
    	    SET @idx = CHARINDEX( @Delim, @Options)
    	  END
    		IF (@Options <> '') BEGIN
    			SET @Id = @Options
    			IF not exists (SELECT * FROM @tbl WHERE Id = @Id)
    		    INSERT INTO @tbl (
    				 Id
    				) VALUES (
    				 @Id
    				);
    		END
    	END
    
      RETURN
    END
    go
    
    
    ALTER PROCEDURE [dbo].[spExample] 
    	-- Add the parameters for the stored procedure here
    	@p1 nvarchar(255) = ''
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	SELECT
    		c.ClientName,
    		c.EngagementNumber,
    		t.EngagementTypeName,
    		c.Active
    	FROM dbo.tblClient AS c
    	LEFT OUTER JOIN dbo.tblEngagementType AS t
    		ON c.EngagementTypeID = t.EngagementTypeID
    	WHERE c.EngagementNumber IN (SELECT Id FROM dbo.SplitList(@p1,',')	
    END
    go
    

    Which, to bring it back on topic, should work with EF Designer just fine. 


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    • Marked as answer by Dogulas Monday, August 29, 2011 1:15 PM
    Tuesday, August 9, 2011 4:17 PM
  • On 8/10/2011 9:31 AM, Dogulas wrote:
    > Jenda,
    >
    >  >>/I do hope you validate the safety of the stuff you pass to such
    > stored procedure ... /
    >
    > Yes, absolutely. As I said this was just a demo.
    >
    > As far as dynamic SQL being a waste, I completely agree. Your table
    > function is *way cool *and will go a long way to eliminating dynamic
    > queries. With your permission and some modification, I would like to use
    > it in my real project.
    >
    > There are some times that I cannot get away from dynamic queries. In
    > some cases huge where clauses and joins are dynamically built based on
    > varied conditions. And in many cases, the procs are not mine and I can't
    > change them. This brings me back to the original question. Why can't
    > Visual Studio find the output columns in this example of a stored procedure?
    >
    > I suspect if the parser finds anything more than a simple select
    > statement, it just gives up. If that is the rule, I'll have to live with
    > it. I would just like to know if this is the case.
    >
     
    Why don't you just use the backdoor if push comes to shove?
     
     
    • Marked as answer by Dogulas Monday, August 29, 2011 1:16 PM
    Wednesday, August 10, 2011 1:41 PM
  • Hi Dogulas,

    I tested your SP, it works when I comment the Drop Table line(--DROP TABLE tblTMP).

    There is no difference between spTest02 and spTest03, they are all select records from Table_01.

    Here is my test:

    ALTER PROCEDURE [dbo].[spTest03]
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	DECLARE @sql nvarchar(512)
    	
    	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblTMP]') AND type in (N'U'))
    	BEGIN
    		DROP TABLE [dbo].[tblTMP]
    	END
    	CREATE TABLE tblTMP
    	(
    		SomeString nvarchar(50) NOT NULL
    	)
    
    	SET @sql = 'Insert Into tblTMP Select Name from Employees'
    	
    	EXECUTE sp_executesql @sql
    
    	SELECT
    		SomeString from tblTMP
    	
    	--DROP TABLE tblTMP
    
    END
    
    
    
    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Dogulas Monday, August 29, 2011 1:17 PM
    Wednesday, August 24, 2011 7:14 AM
    Moderator
  • Hi,

    >>You mentioned if you comment out the drop table line that it works for you.  Does it fail to work for you if you don't comment out this line?

    Yes! If I don't comment out this line, it doesn't work for me.

    I create the Table_1 as follow:

    CREATE TABLE Table_1
    	(
    		SomeString nvarchar(50) NOT NULL
    	)
    


    Then I copy your code spTest03, I didn't change anything.

    ALTER PROCEDURE [dbo].[spTest03]
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	DECLARE @sql nvarchar(512)
    	
    	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblTMP]') AND type in (N'U'))
    	BEGIN
    		DROP TABLE [dbo].[tblTMP]
    	END
    	CREATE TABLE tblTMP
    	(
    		SomeString nvarchar(50) NOT NULL
    	)
    
    	SET @sql = 'Insert Into tblTMP Select * from dbo.Table_1'
    	
    	EXECUTE sp_executesql @sql
    
    	SELECT
    		'STATIC CONTENT 1' AS [FirstColumn],
    		SomeString AS [SecondColumn],
    		GETDATE() AS [ThirdColumn],
    		LEN(SomeString) AS [ThirdColumn]
    	FROM dbo.Table_1
    	ORDER BY SomeString
    	
    	--DROP TABLE tblTMP
    
    END
    

    Here is scree shot here: 

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Dogulas Monday, August 29, 2011 1:17 PM
    Thursday, August 25, 2011 6:18 AM
    Moderator

All replies

  • Hi Dogulas,

    Welcome!

    Would you please share your stored procedure with us? I think you can refer this link: Stored Procedure in EF

    If you want to retrieve records from out parameter, you can refer this blog

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 9, 2011 12:14 PM
    Moderator
  • Alan,

    Thanks for your response.  I created a couple of simpler stored procedures to demonstrate my situation.  Originally my proc looked like this:

    ALTER PROCEDURE [dbo].[spExample] 
    	-- Add the parameters for the stored procedure here
    	@p1 nvarchar(255) = ''
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	DECLARE @sql nvarchar(max) = ''
    
    	SET @sql = '
    		SELECT
    			c.ClientName,
    			c.EngagementNumber,
    			t.EngagementTypeName,
    			c.Active
    		FROM dbo.tblClient AS c
    		LEFT OUTER JOIN dbo.tblEngagementType AS t
    			ON c.EngagementTypeID = t.EngagementTypeID
    		WHERE c.EngagementNumber IN ('
    		+ @p1 +
    		')
    	'
    	
    	--PRINT @sql
    	EXECUTE sp_executesql @sql
    	
    END
    
    

    I realised that determining the output from Dynamic SQL was too much to ask for, so I tried to force a basic select statement and after jumping through some hoops, I came up with this:

    ALTER PROCEDURE [dbo].[spExample2] 
    	-- Add the parameters for the stored procedure here
    	@p1 nvarchar(255) = ''
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	DECLARE @sql nvarchar(max) = ''
    
    	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblTMP]') AND type in (N'U'))
    	BEGIN
    		DROP TABLE [dbo].[tblTMP]
    	END
    	CREATE TABLE tblTMP
    	(
    		ClientName nvarchar(255) NOT NULL,
    		EngagementNumber nvarchar(255) NOT NULL,
    		EngagementType nvarchar(255) NOT NULL,
    		Active nvarchar(255) NOT NULL,
    	)
    
    	SET @sql = '
    		INSERT INTO tblTMP
    		SELECT
    			c.ClientName,
    			c.EngagementNumber,
    			t.EngagementTypeName,
    			c.Active
    		FROM dbo.tblClient AS c
    		LEFT OUTER JOIN dbo.tblEngagementType AS t
    			ON c.EngagementTypeID = t.EngagementTypeID
    		WHERE c.EngagementNumber IN ('
    		+ @p1 +
    		')
    	'
    	
    	--PRINT @sql
    	EXECUTE sp_executesql @sql
    	
    	SELECT
    		ClientName,
    		EngagementNumber,
    		EngagementType,
    		Active
    	FROM tblTMP
    	
    	DROP TABLE tblTMP
    
    END
    
    

    This last proc has a simple select statement.  Why can't Visual Studio find the output columns?

    Thanks,
    Dogulas

    Tuesday, August 9, 2011 1:03 PM
  • It should be enough to test whether the procedure was passed NULL or empty string for the parameter and change the @p1 to a value that will not cause an exception, but rather will cause the procedure to retur an empty recordset. Assuming he EngagementNumber is a non-negative number something like

     

    IF (@p1 is null OR @p1 = '') SET @p1 = -1;

     

    on top of the procedure body should do.


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Tuesday, August 9, 2011 3:18 PM
  • Jenda,

    Thanks for the response.  This wasn't a production proc.  I just whipped out something to demonstrate the problem I posted about.  Your suggestion is good and makes a more error free procedure, but it is unrelated to the problem.

    Thanks for noting it and I will make sure I use something like this in my production code.

    Dogulas

    Tuesday, August 9, 2011 3:47 PM
  • I see. I just tested it and it seems the designer runs the procedure a little differently than I thought so it seems it would not help.

     

    There is another thing though. I do hope you validate the safety of the stuff you pass to such stored procedure as this can easily be a big security hole. Keep in mind that you are constructing an SQL query using the value passed to the procedure. What if the @p1 is not just a comma separated list of numbers, but

    '1) update tblClient set ClientName =  \'bogus\'; select(1'

     

    Apart from this problem you are also forcing the database server to parse the query and build the execution plan each and every time you call this stored procedure. That's a waste!

    IMnsHO it's better to use something like

    CREATE FUNCTION dbo.SplitList(
      @Options varchar(8000),
      @Delim varchar(5)
    ) RETURNS @tbl TABLE(Id Int PRIMARY KEY)
    as
    BEGIN
    	IF (@Options is not NULL) BEGIN
    	  Declare @idx int, @Id Int;
    	  SET @idx = CHARINDEX( @Delim, @Options)
    		IF (@idx = 1) BEGIN
    	    SET @Options = substring( @Options, @idx + 1, 8000);
    	    SET @idx = CHARINDEX( @Delim, @Options);
    		END
    	  WHILE (@idx > 0) BEGIN
    			SET @Id = substring( @Options, 1, @idx - 1)
    			IF not exists (SELECT * FROM @tbl WHERE Id = @Id)
    		    INSERT INTO @tbl (
    				 Id
    				) VALUES (
    				 @Id
    				);
    	    SET @Options = substring( @Options, @idx + 1, 8000)
    	    SET @idx = CHARINDEX( @Delim, @Options)
    	  END
    		IF (@Options <> '') BEGIN
    			SET @Id = @Options
    			IF not exists (SELECT * FROM @tbl WHERE Id = @Id)
    		    INSERT INTO @tbl (
    				 Id
    				) VALUES (
    				 @Id
    				);
    		END
    	END
    
      RETURN
    END
    go
    
    
    ALTER PROCEDURE [dbo].[spExample] 
    	-- Add the parameters for the stored procedure here
    	@p1 nvarchar(255) = ''
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	SELECT
    		c.ClientName,
    		c.EngagementNumber,
    		t.EngagementTypeName,
    		c.Active
    	FROM dbo.tblClient AS c
    	LEFT OUTER JOIN dbo.tblEngagementType AS t
    		ON c.EngagementTypeID = t.EngagementTypeID
    	WHERE c.EngagementNumber IN (SELECT Id FROM dbo.SplitList(@p1,',')	
    END
    go
    

    Which, to bring it back on topic, should work with EF Designer just fine. 


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    • Marked as answer by Dogulas Monday, August 29, 2011 1:15 PM
    Tuesday, August 9, 2011 4:17 PM
  • Jenda,

    >>I do hope you validate the safety of the stuff you pass to such stored procedure ...

    Yes, absolutely. As I said this was just a demo.

    As far as dynamic SQL being a waste, I completely agree. Your table function is way cool and will go a long way to eliminating dynamic queries. With your permission and some modification, I would like to use it in my real project.

    There are some times that I cannot get away from dynamic queries. In some cases huge where clauses and joins are dynamically built based on varied conditions. And in many cases, the procs are not mine and I can't change them. This brings me back to the original question. Why can't Visual Studio find the output columns in this example of a stored procedure?

    I suspect if the parser finds anything more than a simple select statement, it just gives up. If that is the rule, I'll have to live with it. I would just like to know if this is the case.

    Thanks,

    Dogulas

    Wednesday, August 10, 2011 1:31 PM
  • On 8/10/2011 9:31 AM, Dogulas wrote:
    > Jenda,
    >
    >  >>/I do hope you validate the safety of the stuff you pass to such
    > stored procedure ... /
    >
    > Yes, absolutely. As I said this was just a demo.
    >
    > As far as dynamic SQL being a waste, I completely agree. Your table
    > function is *way cool *and will go a long way to eliminating dynamic
    > queries. With your permission and some modification, I would like to use
    > it in my real project.
    >
    > There are some times that I cannot get away from dynamic queries. In
    > some cases huge where clauses and joins are dynamically built based on
    > varied conditions. And in many cases, the procs are not mine and I can't
    > change them. This brings me back to the original question. Why can't
    > Visual Studio find the output columns in this example of a stored procedure?
    >
    > I suspect if the parser finds anything more than a simple select
    > statement, it just gives up. If that is the rule, I'll have to live with
    > it. I would just like to know if this is the case.
    >
     
    Why don't you just use the backdoor if push comes to shove?
     
     
    • Marked as answer by Dogulas Monday, August 29, 2011 1:16 PM
    Wednesday, August 10, 2011 1:41 PM
  • darnold924,

    Because I didn't know about the back door.  Thanks!

    Dogulas

    Wednesday, August 10, 2011 1:48 PM
  • On 8/10/2011 9:48 AM, Dogulas wrote:
    > darnold924,
    >
    > Because I didn't know about the back door. Thanks!
    >
     
     
     
    Wednesday, August 10, 2011 1:52 PM
  • darnold924,

    Thanks again.  I'd still like an answer to my question though.

    Why can't Visual Studio find the output columns in this example of a stored procedure?

    Thanks,

    Dogulas

     

    Wednesday, August 10, 2011 2:19 PM
  • On 8/10/2011 10:19 AM, Dogulas wrote:
    > darnold924,
    >
    > Thanks again. I'd still like an answer to my question though.
    >
    > Why can't Visual Studio find the output columns in this example of a
    > stored procedure?
    >
     
    I don't know. But I suspect that the columns must be properties of an
    existing entity on the model. I don't think you can have an sproc that
    is returning a set of columns that has no on the model.
     
    I think EF knows nothing about those columns/properties if they are not
    a part of an entity on the model, which are not included in the resultset.
     
    Wednesday, August 10, 2011 2:45 PM
  • On 8/10/2011 10:19 AM, Dogulas wrote:
    > Why can't Visual Studio find the output columns in this example of a
    > stored procedure?
     
    I don't know. But I suspect that the columns must be properties of an
    existing entity on the model. I don't think you can have an sproc that
    is returning a set of columns that has no on the model.
     
    I think EF knows nothing about those columns/properties if they are not
    a part of an entity on the model, which are not included in the resultset.
     

    Nope. There's no reason the columns would need to resemble anything you have in the model so far.

     

    The EF designer first queries the database to find out what kind of database and what version it is, then queries the stored procedure's parameters and then "executes" the procedure with the FMTONLY option set to ON. According to the MSSQL docs:

    SET FMTONLY

    Returns only metadata to the client. Can be used to test the format of the response without actually running the query.

    No rows are processed or sent to the client because of the request when SET FMTONLY is turned ON.

    The setting of SET FMTONLY is set at execute or run time and not at parse time.

     

     


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Wednesday, August 10, 2011 5:27 PM
  • On 8/10/2011 1:27 PM, JendaPerl wrote:
    >     On 8/10/2011 10:19 AM, Dogulas wrote:
    >      > Why can't Visual Studio find the output columns in this example of a
    >      > stored procedure?
    >     I don't know. But I suspect that the columns must be properties of an
    >     existing entity on the model. I don't think you can have an sproc that
    >     is returning a set of columns that has no on the model.
    >     I think EF knows nothing about those columns/properties if they are not
    >     a part of an entity on the model, which are not included in the
    >     resultset.
    >
    > Nope. There's no reason the columns would need to resemble anything you
    > have in the model so far.
     
    I think that some type of entity has to be on the model in order to map
    to something from the sproc resultset. I think it can be a custom entity
    that is placed on the model for the purpose of mapping the results from
    the sproc to something.
     
     
    <copied>
     
    In the post I’m going to explain how to map results of a stored
    procedure to a custom created entity which we have created with the
    Entity Framework designer.
     
    <end>
     
    Wednesday, August 10, 2011 5:48 PM
  • After the designer finds out what columns the stored procedure returns, it will allow you to create a complex type (not entity) with those columns. 

    Of course if you know the stored procedure returns complete entities you can specify that in the function mapping dialogbox and if there is already a complext type that matches the stored procedure result you can select the complext type, but you do not need to create neither an entity nor a complex type before importing and mapping the stored procedure. 

     

    Jenda


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Wednesday, August 10, 2011 10:15 PM
  • Anyone interrested,

    I created a simple test database with one table which contained one column of type nvarchar(50).  I then created four stored procedures to test with.  I will list them below.  When executing each of the procs they each return the exact same dataset.  If you examine the returned dataset, only one of the columns contains values from the single table and the rest are created by the proc.

    I then created a test project and created a entity data model for the database.  In this EDM, I only included the four procs.  I did NOT include the one table.  I tried to create a function import for each of the procs.  On the Function Import page, I selected the proc and clicked on the "Get Column Information" button.  The columns were correctly discovered for procs 1, 2, and 4, but proc 3 returned "the selected stored procedure returned no columns".

    I don't know what it is about proc 3 that causes the column investigation to fail.  I even tried it with the line "DROP TABLE tblTMP" commented out, but it still failed.  Anyone have any ideas?

    By the way, this is purely academic and a curiosity.  I've moved on in my project and am not being held up.

    Thanks,
    Dogulas

    ALTER PROCEDURE [dbo].[spTest01] 
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	SELECT
    		'STATIC CONTENT 1' AS [FirstColumn],
    		SomeString AS [SecondColumn],
    		GETDATE() AS [ThirdColumn],
    		LEN(SomeString) AS [ThirdColumn]
    	FROM dbo.Table_1
    	ORDER BY SomeString
    
    END
    
    

    ALTER PROCEDURE [dbo].[spTest02] 
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	DECLARE @sql nvarchar(512)
    
    	SET @sql = 'Select * Into #TMP from dbo.Table_1'
    	
    	EXECUTE sp_executesql @sql
    
    	SELECT
    		'STATIC CONTENT 1' AS [FirstColumn],
    		SomeString AS [SecondColumn],
    		GETDATE() AS [ThirdColumn],
    		LEN(SomeString) AS [ThirdColumn]
    	FROM dbo.Table_1
    	ORDER BY SomeString
    
    END
    
    

    ALTER PROCEDURE [dbo].[spTest03]
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	DECLARE @sql nvarchar(512)
    	
    	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblTMP]') AND type in (N'U'))
    	BEGIN
    		DROP TABLE [dbo].[tblTMP]
    	END
    	CREATE TABLE tblTMP
    	(
    		SomeString nvarchar(50) NOT NULL
    	)
    
    	SET @sql = 'Insert Into tblTMP Select * from dbo.Table_1'
    	
    	EXECUTE sp_executesql @sql
    
    	SELECT
    		'STATIC CONTENT 1' AS [FirstColumn],
    		SomeString AS [SecondColumn],
    		GETDATE() AS [ThirdColumn],
    		LEN(SomeString) AS [ThirdColumn]
    	FROM dbo.Table_1
    	ORDER BY SomeString
    	
    	--DROP TABLE tblTMP
    
    END
    
    

    ALTER PROCEDURE [dbo].[spTest04] 
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	DECLARE @sql nvarchar(512)
    
    	SELECT
    		'STATIC CONTENT 1' AS [FirstColumn],
    		SomeString AS [SecondColumn],
    		GETDATE() AS [ThirdColumn],
    		LEN(SomeString) AS [ThirdColumn]
    	FROM dbo.Table_1
    	ORDER BY SomeString
    	
    	SET @sql = 'nonsense'
    
    END
    
    

     

     

     

    Thursday, August 11, 2011 3:05 PM
  • Hi Dogulas,

    I tested your SP, it works when I comment the Drop Table line(--DROP TABLE tblTMP).

    There is no difference between spTest02 and spTest03, they are all select records from Table_01.

    Here is my test:

    ALTER PROCEDURE [dbo].[spTest03]
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	DECLARE @sql nvarchar(512)
    	
    	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblTMP]') AND type in (N'U'))
    	BEGIN
    		DROP TABLE [dbo].[tblTMP]
    	END
    	CREATE TABLE tblTMP
    	(
    		SomeString nvarchar(50) NOT NULL
    	)
    
    	SET @sql = 'Insert Into tblTMP Select Name from Employees'
    	
    	EXECUTE sp_executesql @sql
    
    	SELECT
    		SomeString from tblTMP
    	
    	--DROP TABLE tblTMP
    
    END
    
    
    
    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Dogulas Monday, August 29, 2011 1:17 PM
    Wednesday, August 24, 2011 7:14 AM
    Moderator
  • Alen Chen,

    Thanks for your response.  Of course you know this deepens the mystery.  My third stored procedure consistantly fails to find the columns.

    *)  You mentioned if you comment out the drop table line that it works for you.  Does it fail to work for you if you don't comment out this line?

    *)  You mentioned there is no difference between spTest02 and spTest03, but there is.  In spTest03 the query is exactly the same as in spTest02 but in this latter case it does some other unrelated work before the query.  I was attempting to test whether the complexity of the stored procedure influenced the ability to detect the columns.  As a case in point, since the complexity is the only difference between my spTest02 and spTest03 and because spTest02 always works and spTest03 always fails, I'd say there is something to support this theory.

    *)  If your stored procedure is meant to be equivilant to my spTest03 stored procedure, I'm afraid there are some differences.  Specifically, you are selecting from the newly created table and not from the pre-existing table.  In order to duplicate my results you would need to pre-create a table (dbo.Tabel_1) that contains one column (SomeString nvarchar(50) NOT NULL).  Your query should select from that table.  I think it is the complexity that causes the problem.

    Thanks again,
    Dogulas

    Wednesday, August 24, 2011 1:03 PM
  • Hi,

    >>You mentioned if you comment out the drop table line that it works for you.  Does it fail to work for you if you don't comment out this line?

    Yes! If I don't comment out this line, it doesn't work for me.

    I create the Table_1 as follow:

    CREATE TABLE Table_1
    	(
    		SomeString nvarchar(50) NOT NULL
    	)
    


    Then I copy your code spTest03, I didn't change anything.

    ALTER PROCEDURE [dbo].[spTest03]
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	DECLARE @sql nvarchar(512)
    	
    	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblTMP]') AND type in (N'U'))
    	BEGIN
    		DROP TABLE [dbo].[tblTMP]
    	END
    	CREATE TABLE tblTMP
    	(
    		SomeString nvarchar(50) NOT NULL
    	)
    
    	SET @sql = 'Insert Into tblTMP Select * from dbo.Table_1'
    	
    	EXECUTE sp_executesql @sql
    
    	SELECT
    		'STATIC CONTENT 1' AS [FirstColumn],
    		SomeString AS [SecondColumn],
    		GETDATE() AS [ThirdColumn],
    		LEN(SomeString) AS [ThirdColumn]
    	FROM dbo.Table_1
    	ORDER BY SomeString
    	
    	--DROP TABLE tblTMP
    
    END
    

    Here is scree shot here: 

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Dogulas Monday, August 29, 2011 1:17 PM
    Thursday, August 25, 2011 6:18 AM
    Moderator
  • Alan,

    Thanks for your response.

    Ok, I stand corrected.  I retried my query with the drop commented out and got the exact same results as you.  Sorry for my confusion.

    So, is it safe to say that the query that produces the columns must be the last thing in the stored procedure?  Is the drop causing the problem because it occurred after the query?

    Thanks,
    Dogulas

    Friday, August 26, 2011 4:50 PM