Store procedure

Results 1 to 5 of 5

Thread: Store procedure

  1. #1
    Join Date
    Dec 1969
    Posts
    141

    Default Store procedure

    is it possible to change<BR><BR>CREATE Procedure sp_MyTop5<BR> (<BR> @MemberID integer,<BR> @strTitle1 varchar (255),<BR> @strTitle2 varchar (255),<BR> @strTitle3 varchar (255),<BR> @strTitle4 varchar (255),<BR> @strTitle5 varchar (255)<BR> )<BR>As<BR>Declare @iTop1 tinyint, @iTop2 tinyint, @iTop3 tinyint, @iTop4 tinyint, @iTop5 tinyint<BR><BR> SET @iTop1 = 1<BR> SET @iTop2 = 2<BR> SET @iTop3 = 3<BR> SET @iTop4 = 4<BR> SET @iTop5 = 5<BR><BR>IF EXISTS(SELECT &#039;Exists&#039; FROM MyTop5 WHERE MemberID = @MemberID AND iOrder=@iTop1)<BR> UPDATE MyTop5 SET strTitle=@strTitle1 WHERE MemberID=@MemberID AND iOrder=@iTop1<BR>ELSE<BR> INSERT INTO MyTop5 (MemberID,strTitle,iOrder) VALUES (@MemberID,@strTitle1,@iTop1)<BR><BR>IF EXISTS(SELECT &#039;Exists&#039; FROM MyTop5 WHERE MemberID = @MemberID AND iOrder=@iTop2)<BR> UPDATE MyTop5 SET strTitle=@strTitle2 WHERE MemberID=@MemberID AND iOrder=@iTop2<BR>ELSE<BR> INSERT INTO MyTop5 (MemberID,strTitle,iOrder) VALUES (@MemberID,@strTitle2,@iTop2)<BR><BR>IF EXISTS(SELECT &#039;Exists&#039; FROM MyTop5 WHERE MemberID = @MemberID AND iOrder=@iTop3)<BR> UPDATE MyTop5 SET strTitle=@strTitle3 WHERE MemberID=@MemberID AND iOrder=@iTop3<BR>ELSE<BR> INSERT INTO MyTop5 (MemberID,strTitle,iOrder) VALUES (@MemberID,@strTitle3,@iTop3)<BR><BR>IF EXISTS(SELECT &#039;Exists&#039; FROM MyTop5 WHERE MemberID = @MemberID AND iOrder=@iTop4)<BR> UPDATE MyTop5 SET strTitle=@strTitle4 WHERE MemberID=@MemberID AND iOrder=@iTop4<BR>ELSE<BR> INSERT INTO MyTop5 (MemberID,strTitle,iOrder) VALUES (@MemberID,@strTitle4,@iTop4)<BR><BR>IF EXISTS(SELECT &#039;Exists&#039; FROM MyTop5 WHERE MemberID = @MemberID AND iOrder=@iTop5)<BR> UPDATE MyTop5 SET strTitle=@strTitle5 WHERE MemberID=@MemberID AND iOrder=@iTop5<BR>ELSE<BR> INSERT INTO MyTop5 (MemberID,strTitle,iOrder) VALUES (@MemberID,@strTitle5,@iTop5)<BR><BR><BR><BR>To <BR><BR>CREATE Procedure sp_MyTop5<BR> (<BR> @MemberID integer,<BR> @strTitle1 varchar (255),<BR> @strTitle2 varchar (255),<BR> @strTitle3 varchar (255),<BR> @strTitle4 varchar (255),<BR> @strTitle5 varchar (255)<BR> )<BR>As<BR>Declare @iTop tinyint<BR>Declare @strTitle varchar (255)<BR><BR>SET @iTop = 1<BR><BR>WHILE @iTop &#060;6<BR>BEGIN<BR>-----------------------------------------------------------------<BR><BR> SET @strTitle = @strTitle+CONVERT(VARCHAR (4), @iTop)????<BR><BR>-----------------------------------------------------------------<BR> IF EXISTS(SELECT &#039;Exists&#039; FROM MyTop5 WHERE MemberID = @MemberID AND iOrder=@iTop)<BR> UPDATE MyTop5 SET strTitle=@strTitle WHERE MemberID=@MemberID AND iOrder=@iTop<BR> ELSE<BR> INSERT INTO MyTop5 (MemberID,strTitle,iOrder) VALUES (@MemberID,@strTitle,@iTop)<BR><BR> SET @iTop = @iTop+1<BR>END<BR>

  2. #2
    Join Date
    Dec 1969
    Posts
    4

    Default RE: Store procedure

    Nice try but I don&#039;t think you can. You need something like the Eval function in JavaScript which will evaluate your<BR><BR>strTitle + Convert* etc..<BR><BR>to strTitle1 and assign that result to @strTitle.<BR><BR>I don&#039;t think T-SQL has anything like that.

  3. #3
    Join Date
    Dec 1969
    Posts
    7,686

    Default it has

    EXECUTE ( @some_string )

  4. #4
    Join Date
    Dec 1969
    Posts
    141

    Default RE: it has

    U mean<BR><BR>SET @strTitle = <BR>EXECUTE (@strTitle+CONVERT(VARCHAR (4), @iTop)) ???


  5. #5
    Join Date
    Dec 1969
    Posts
    4

    Default Thanks for that

    That&#039;s brand new to me and I am sure will prove useful. <BR><BR>Thanks

Posting Permissions

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