Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jun 2011
    Posts
    63

    Unanswered: Is this t-sql correct?

    Hi guys, im implementing a map creating sp and was wondering if i took the correct path with this:

    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE createMap
    	@status tinyint OUTPUT,
    	@mapName char(30),
    	@mapSize tinyint,
    	@backgroundImage char(100)
    
    AS
    
    BEGIN TRY
    
    BEGIN TRANSACTION
    
    	SET NOCOUNT ON;
    	Insert Into Maps ([mapName], backgroundImage, mapSize)
    	values (@mapName, @backgroundImage, @mapSize)
    	
    	DECLARE @mapId integer
    	SET @mapId = SCOPE_IDENTITY()
    	
    	if @mapSize = 9
    	BEGIN
    		INSERT INTO mapGrids (tooltip, alternateText, imageUrl, linkId, mapId, nodeName)
    		VALUES (null ,null, null, REPLACEME, @mapId, (@mapName + '_01')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_02')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_03')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_04')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_05')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_06')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_07')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_08')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_09'))
    	END
    	
    	if @mapSize = 16
    	BEGIN
    		INSERT INTO mapGrids (tooltip, alternateText, imageUrl, linkId, mapId, nodeName)
    		VALUES (null ,null, null, REPLACEME, @mapId, (@mapName + '_01')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_02')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_03')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_04')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_05')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_06')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_07')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_08')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_09')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_10')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_11')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_12')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_13')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_14')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_15')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_16'))
    	END
    	
    	if @mapSize = 25
    	BEGIN
    		INSERT INTO mapGrids (tooltip, alternateText, imageUrl, linkId, mapId, nodeName)
    		VALUES (null ,null, null, REPLACEME, @mapId, (@mapName + '_01')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_02')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_03')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_04')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_05')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_06')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_07')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_08')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_09')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_10')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_11')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_12')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_13')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_14')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_15')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_16')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_17')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_18')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_19')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_20')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_21')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_22')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_23')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_24')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_25'))
    	END
    	
    	if @mapSize = 36
    	BEGIN
    		INSERT INTO mapGrids (tooltip, alternateText, imageUrl, linkId, mapId, nodeName)
    		VALUES (null ,null, null, REPLACEME, @mapId, (@mapName + '_01')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_02')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_03')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_04')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_05')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_06')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_07')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_08')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_09')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_10')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_11')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_12')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_13')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_14')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_15')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_16')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_17')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_18')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_19')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_20')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_21')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_22')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_23')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_24')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_25')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_26')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_27')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_28')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_29')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_30')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_31')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_32')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_33')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_34')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_35')),
    			   (null ,null, null, REPLACEME, @mapId, (@mapName + '_36'))
    	END
    	
    SET @status = 1	
    	
    COMMIT
    
    END TRY
    BEGIN CATCH	
    
    	SET @status = 0
    
    IF @@TRANCOUNT>0
    	ROLLBACK
    
    END CATCH
    
    RETURN @status
    I havent tested it but it probably works. My problem is that i used a lot of redundant code but i dont know how to go around this problem (if only this was c#, heh).

    thanks!

  2. #2
    Join Date
    Jun 2011
    Posts
    63
    Bump 123456

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dude, bumping isn't gonna help

    we're waiting until you actually test your code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2011
    Posts
    63
    The thing is, i cant run that code yet because of constraints. Here is how i need to to insert the record:

    http://img837.imageshack.us/img837/4771/unledck.jpg

    I first need to add an entry in LinkTypes and LinkTo, then in Links, and then in mapGrid.

    Thats why its so complicated!

    See i know how to do it, but it would end up being over 300 lines.

    Whats the best way to do it in my case?

  5. #5
    Join Date
    Jun 2011
    Posts
    63
    I came up with this:

    Code:
    USE [dbTalesOfEpic]
    GO
    /****** Object:  StoredProcedure [dbo].[createMap]    Script Date: 07/30/2011 22:02:28 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- Batch submitted through debugger: SQLQuery3.sql|7|0|C:\Users\Dassier Rizzo\AppData\Local\Temp\~vs5E23.sql
    
    ALTER PROCEDURE [dbo].[createMap]
    	@status tinyint OUTPUT,
    	@mapName char(30),
    	@mapSize tinyint,
    	@backgroundImage char(100)
    
    AS
    
    DECLARE @mapId integer,
            @i tinyint,
            @a integer,
            @b integer
    
    BEGIN TRY
    
    BEGIN TRANSACTION
    
    	SET NOCOUNT ON;
    	
    	Insert Into Maps ([mapName], backgroundImage, mapSize)
    	values (@mapName, @backgroundImage, @mapSize)
    	
    	SET @i = 0
    	SET @mapId = SCOPE_IDENTITY()
    	
    	WHILE @i < @mapSize
    	BEGIN
    		INSERT INTO linkTo (mapLink, locationLink)
    		VALUES (null, null)
    		SET @a = SCOPE_IDENTITY() ---Stores the pk for insert into foreign table---
    		INSERT INTO Links (linksToId, linkTypeId)
    		VALUES (@a, 2) ---@s is the pk of the previous table, and 2 is the entry in linkTypes specifying noLink---
    		SET @b = SCOPE_IDENTITY() ---Stores the pk for insert into foreign table---
    		INSERT INTO mapGrids (tooltip, alternateText, imageUrl, linkId, mapId, nodeName)
    		VALUES (null ,null, null, @b, @mapId, (@mapName + '_' + @i))
    		SET @i = @i + 1
    	END
    	
    SET @status = 1	
    	
    COMMIT
    
    END TRY
    BEGIN CATCH	
    
    	SET @status = 0
    
    IF @@TRANCOUNT>0
    	ROLLBACK
    
    END CATCH
    
    RETURN @status
    What do you think?

    It isnt working thou.... must be a bug somewhere. Gonna try and get the sql server debugger to work and see where it is.
    Last edited by TheGateKeeper; 07-30-11 at 17:07.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dude, we don't know the requirements, and you don't give us any information as to what is "not working" on it.
    WTF?
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Jun 2011
    Posts
    63
    Sorry about the lack of info, but i got it to work except one bit.

    I have this expression:

    CAST ( (@mapName + '_' + CAST((@i) AS CHAR (10))) AS Char(30))

    That is meant to generate a mapname. Basically, @i is the loop counterm and @mapName is the original map name. When the original map name is set to "Test", i want it to output Test_01, Test)02, etc. But all it outputs is Test.

    Why is this?

    Thanks!

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dump the superfluous parenthesis.
    CAST (@mapName + '_' + CAST(@i AS CHAR (10)) AS Char(30))
    Now, what is the datatype for @mapName?
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Jun 2011
    Posts
    63
    Char (30)



    123456

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You may want to use varchar, rather than char. Char usually pads out with spaces:
    Code:
    declare @map char(30)
    
    set @map = 'hello'
    
    select @map + 'a'

  11. #11
    Join Date
    Jun 2011
    Posts
    63
    Oh so that is why i have padding on all my fields?

    I will use varchar then...

    How do i apply that to my problem?

    CAST ( (@mapName + '_' + CAST((@i) AS CHAR (10))) AS Char(30))

    where @mapName is a char
    and @i is a tinyint

    I want to end result to be @mapname_@i

    Thanks

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Because you are taking a value which is already 30 characters long, adding a few more characters to the end, and then truncating it back to 30 characters.
    If it's not practically useful, then it's practically useless.

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

  13. #13
    Join Date
    Jun 2011
    Posts
    63
    I see what you mean... is there something equivalent to Trim in tsql? Or do i have to use varchar which doesnt pad the values? My code would work with var char right?

  14. #14
    Join Date
    Jun 2011
    Posts
    63
    Ok so i fixed it by doing:

    CAST ( (RTRIM(@mapName) + '_' + RTRIM(CAST((@i) AS CHAR (10)))) AS Char(30))

    But a question... how do i tell the database to not store spaces at the end of data? Such as if i enter "hello" in a char(6), when i select it "hello" is selected and not "hello " (with the trailing space).

    Thanks

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by mcrowley View Post
    you may want to use varchar, rather than char......
    . .

Posting Permissions

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