Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2007
    Posts
    11

    Unanswered: Question About Stored Procedure and Variable From Dynamic SQL

    Ok, so I have this stored procedure that Inserts a record and I need to return the ID inserted to pass to another procedure. I ended up having to use sp_executesql because I need to dynamically add the schema to the table. The issue that I am having is that the variable that I try to pass to the other procedure always ends up null, but it shows the correct output. Just to clarify on this, i am an Applications developer and not a DBA so I may be going about this all wrong. Any help on this would be greatly appreciated.

    Here is the SP
    Code:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[sp_AddAnalyte] 
    	@Analyt NVarChar(100), 
    	@LName NVarChar(100), 
    	@AtomicNum NVarChar(100), 
    	@AtomicMass NVarChar(100), 
    	@Weight NVarChar(100), 
    	@HalfLife NVarChar(100), 
    	@Units NVarChar(100), 
    	@HF_Secs NVarChar(100), 
    	@Gas NVarChar(100), 
    	@Nat NVarChar(100)
    	AS
    	DECLARE @ID INT,@i INT
    	DECLARE @schema VarChar(50)
    	DECLARE @sql NVARCHAR(512)
    	BEGIN
    		SET @Schema = (SELECT schema_name())
    		SET @sql = N'INSERT INTO [' + @schema + '].[Analytes] ([Analyte],[LongName],[AnalyteType]) VALUES (''' + @Analyt + ''',''' + @LName + ''',1)';
    		EXEC sp_executesql @query = @sql;
    		SET @sql = N'SELECT MAX(AnalytePK) FROM [' + @schema + '].[Analytes]'; 
    		EXEC sp_executesql 
    			@query = @sql, 
    		    @params = N'@ID INT OUTPUT', 
    		    @i = @ID OUTPUT
    		EXEC sp_AddParameterValue 6, @i, 'AtomicNum', @AtomicNum, '';
    		EXEC sp_AddParameterValue 6, @i, 'AtomicMass', @AtomicMass, '';
    		EXEC sp_AddParameterValue 6, @i, 'Weight', @Weight, '';
    		EXEC sp_AddParameterValue 6, @i, 'HalfLife', @HalfLife, '';
    		EXEC sp_AddParameterValue 6, @i, 'Units', @Units, ''; 
    		EXEC sp_AddParameterValue 6, @i, 'HF_Secs', @HF_Secs, '';
    		EXEC sp_AddParameterValue 6, @i, 'Gas', @Gas, '';
    		EXEC sp_AddParameterValue 6, @i, 'Natural', @Nat, '';
    	END

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I think you want to do this:

    SET @sql = N'SELECT @ID = MAX(AnalytePK) FROM [' + @schema + '].[Analytes]';

  3. #3
    Join Date
    Jan 2007
    Posts
    11
    That fixed it, thank you very much.

  4. #4
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Just wondering... Why do you use dynamic SQL? Do you have an undefined number of schema's? If so, again, why? It doesn't sound like good programming

    If you have predefined schema's it's better to make a sp for each possible schema (without dynamic SQL). You can alter the sp_AddAnalyte sp so it executes the right schema-bound sp depending on the schema.

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    he's probably long gone now...

  6. #6
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Couldn't resist, looks like a disaster about to happen...

  7. #7
    Join Date
    Jan 2007
    Posts
    11
    No, I am not gone. The application that we are developing has and ability to manage multiple "Sites" as we call them. Every Site will have the same database structure and we did not want to intermingle their data. We decided to create a schema for each site. (Keep in mind that I am not a DBA). The application needs to be able to handle any number of sites. Do you have any suggestions as to how to accomplish this in another manner? I thought about creating Multiple databases, (one for each site), but in the end we decided to use schemas. I would appreciate any input on this because, like I said, I am not a DBA. Thanks guys,

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    first of all, why don't you want to intermingle data from different sites in the same tables?

    second, if you did what lexiflex suggests, you wouldn't have to use dynamic sql. so if your number of sites is fixed at N, you could just have N versions of this sproc, each in one of N schemas, each with the correct schema hardcoded. that way you avoid dynamic sql.

    It's not really a workable solution if N is growing in time. but neither is what you are planning workable if N grows. Any design where the number of objects grows when you add a new customer should be re-thought, IMO. how maintainable will it be when you are supporting 100 sites? how about 10000 sites?

  9. #9
    Join Date
    Jan 2007
    Posts
    11
    To answer your first question, The application is used to manage sample data for various sites. Different sites will be sampling different media types all with different parameters and characteristics. For example we have a units table. At site A where they are handling Radioactive samples the units will be things like pCi/L, mR/Hr etc... at side B they are handling only Non Rad material and they would have no use for these Units. The idea was to not intermingle the data, because these lists (and the one I gave is a very simple example) can grow to be quite large. I was hoping to come up with a method to store the data so that Site B would not have to sort through all of Site A's data to find the data suited for their samples. Another method I have thought of was adding a siteID column to the tables. Is their anything wrong with using that method? This would keep all the data in a single database and would allow me to have predefined schemas, and would also allow me to keep the data separate. Any thoughts?

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    My instinct would be to add a SiteID column to the appropriate tables and intermingle the data. That way when you want to add another site, all you do is add a row to your Site table, rather than adding X new tables, X new sprocs, etc. Much more maintainable.

    If you wanted, you could then create views for each site that would return data only for that site. If you granted a site customer perms only on that view, and not on the underlying table, they wouldn't get confused by some other site's data because they would only ever query the view.

    One final point: it looks like, based on the sp_AddParameterValue proc, that you may be using the EAV model. This model is very difficult to work with IMO. See this blog post for a nice essay on the many pitfalls of EAV:
    http://weblogs.sqlteam.com/davidm/articles/12117.aspx

  11. #11
    Join Date
    Jan 2007
    Posts
    11
    Thank you for all your help on this. I think it is about time to rethink the database design

  12. #12
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Although jezemine's suggestion seems the most favorable to me at this moment, a possible drawback can be when you want to limit the data size of a site.

  13. #13
    Join Date
    Jan 2007
    Posts
    11
    Quote Originally Posted by Lexiflex
    Although jezemine's suggestion seems the most favorable to me at this moment, a possible drawback can be when you want to limit the data size of a site.
    I am not really worried about limiting the size of a site. For the most part, this application is going to be used by companies that are only really worried about 1 or 2 sites, but my employers were determined to market it as being able to handle infinite sites, which will most likely never truly exceed 10 or 20.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    My instinct would be to have common tables of media types and parameters, and then have a table which shows which records to display for which sites. A site could call sprocs to get its lists.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •