Post reply

Newbie to SP

  • April 28, 2009 at 5:41 am

    #203440

    Hi all,

    I have a table called ContestOption.

    I have 3 fields in it..

    namely,

    1.ContestOptionID

    2.ContestID

    3.ContestOption

    I have a method like this,

    public void CreateOptions(long ContestID, string Options,string CorrectOpt)

    {

    int status = 0;

    long CorrectOptID = -1;

    Database db = DatabaseFactory.CreateDatabase();

    SqlCommand cmdSQL = new SqlCommand("");

    cmdSQL.Parameters.AddWithValue("@ContestId", ContestID);

    cmdSQL.Parameters.AddWithValue("@Options", Options);

    cmdSQL.Parameters.AddWithValue("@CorrectOption", CorrectOpt);

    SqlParameter output = new SqlParameter("@CorrectOptID", SqlDbType.BigInt);

    output.Direction = ParameterDirection.Output;

    cmdSQL.Parameters.Add(output);

    cmdSQL.CommandType = CommandType.StoredProcedure;

    try

    {

    status = db.ExecuteNonQuery(cmdSQL);

    CorrectOptID = Convert.ToInt64(cmdSQL.Parameters["@CorrectOptID"].Value.ToString());

    }

    catch (Exception ex)

    {

    CommonUtilities.WriteErrorInXML(ex.Message);

    }

    }

    Now my problem is that i don know how to write the stored procedure so that the contest options are split and stored row by row and the correct option is returned.

    In the method above,

    String options is concatinated using many strings,

    like,

    String1|String2|string3|string4 and so on...

    So in the SP itself it should be split and inserted into that table..

    Plz Plz Help me.

  • April 28, 2009 at 5:51 am

    #984984

    search the "Scripts" section here on SSC for "Split Function"

    there are a lot of great contributions there;

    they typically expect a varchar string and a delimiter to split them by:

    for example:

    select stuff from yourtable where somevalue in dbo.Split(@String,'|')

    that will do exactly what you are looking for;

    I would suggest using one based on a Tally table, there's a great article by Jeff Moden here that uses a Tally table to split 1,2 and 3 dimential arrays from your passed in varchar string:

    http://www.sqlservercentral.com/articles/T-SQL/63003/[/url]

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 28, 2009 at 6:00 am

    #984989

    THe code below is doing what exactly you want , It is taking a comma separated string and then filling each value in the temporary table.you can repalce the comma by '|' and temporart table by your table name and whatever fields you want .

    hope it help;

    DECLARE @sql varchar(1600)

    CREATE TABLE #TempList

    (

    tankAltName varchar(9)

    )

    DECLARE @tankalt varchar(9), @Position int

    SET @tankAltName = LTRIM(RTRIM(@TankAltName))+ ','

    SET @Position = CHARINDEX(',', @tankAltname, 1)

    IF REPLACE(@tankAltName, ',', '') ''

    BEGIN

    WHILE @Position > 0

    BEGIN

    SET @tankAlt = LTRIM(RTRIM(LEFT(@TankALtName, @Position - 1)))

    IF @TankALt ''

    BEGIN

    INSERT INTO #TempList (tankAltName) VALUES (CAST(@TankALt AS varchar))

    END

    SET @TankAltName = RIGHT(@TankAltName, LEN(@TankALtName) - @Position)

    SET @Position = CHARINDEX(',', @TankAltName, 1)

    END

    END

    :w00t:

  • April 28, 2009 at 6:09 am

    #984999

    Thank u So much ill work on it right away.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply