Need to Convert Text Field to Varchar Temporarily So That I Can Pass to a Stored Procedure

Need to convert Text field to Varchar temporarily so that I can pass to a stored procedure

Declare the variable of type varchar(8000)

declare @v varchar(8000)
SET @v = (SELECT CAST(textcol as varchar(8000)) FROM yourtable WHERE ....)

Obviously it might still be truncated but not at 1 character.

Client database is using Text field, I'm using nvarchar

If you think about smaller set of special characters to replace/delete, you can use nested REPLACE() in your SELECT:

-- preparation of the example
CREATE TABLE #tt (id int identity (1,1), col text)
GO
INSERT INTO #tt (col) VALUES (N'this is a text
multiline
3rd line')
GO
-- run of the example
SELECT REPLACE(REPLACE(REPLACE(CAST(col as varchar(MAX)) ,
    CHAR(9), '<9>'),    -- replace '<9>' for '' to achieve removal
    CHAR(10), '<10>'),  
    CHAR(13), '<13>') AS NewText
    FROM #tt

--cleanup
--DROP TABLE #tt

Output:

(1 row(s) affected)
NewText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
this is a text<13><10>multiline<13><10>3rd line

(1 row(s) affected)

Problem inserting data into a varchar field using a stored procedure

This problem was caused by the 3rd party software that we used to create the linked server to the DB2 database. This could not handle a field 2000 characters long when run via a stored procedure but could when run interactively.

I ultimately got around the problem by splicing the field into 10 200 character long fields for the upload and then re-joined them again when they were in the SQL database.

Passing multiple parameters within a single parameter to a Stored Procedure

Just another option is a conditional aggregation (it may be a little cleaner).

Example

Declare @S varchar(max) ='Ref=2211010001165381;Src=ONLN;,Ref=2211010001165481;Src=ONLN;,Ref=2211010001165581;Src=ONLN;'

Select Ref = max(case when B.value like 'Ref=%' then substring(B.value,5,250) end)
      ,Src = max(case when B.value like 'Src=%' then substring(B.value,5,250) end)
 From string_split(@S,',') A
 Cross Apply  string_split(A.value,';')  B
 Group By A.value

Results

Ref                 Src
2211010001165381    ONLN
2211010001165481    ONLN
2211010001165581    ONLN

Convert INT to VARCHAR SQL

Use the convert function.

SELECT CONVERT(varchar(10), field_name) FROM table_name

Passing a varchar full of comma delimited values to a SQL Server IN function

Don't use a function that loops to split a string!, my function below will split a string very fast, with no looping!

Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

use this function to split your string, which does not loop and is very fast:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn              char(1)              --REQUIRED, the character to split the @List string on
    ,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
    ListValue varchar(500)
)
AS
BEGIN

/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.

Returns a table, one row per item in the list, with a column name "ListValue"

EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')

    returns:
        ListValue  
        -----------
        1
        12
        123
        1234
        54321
        6
        A
        *
        |||
        B

        (10 row(s) affected)

**/

----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
        (ListValue)
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

RETURN

END --Function FN_ListToTable

you can use this function as a table in a join:

SELECT
    Col1, COl2, Col3...
    FROM  YourTable
        INNER JOIN FN_ListToTable(',',@YourString) s ON  YourTable.ID = s.ListValue

Here is your example:

Select * from sometable where tableid in(SELECT ListValue FROM dbo.FN_ListToTable(',',@Ids) s)

How to convert varchar2 input parameter to timestamp in stored procedure

Assuming that your input is in ISO8601 format with fractional seconds and a time zone then:

CREATE OR REPLACE PROCEDURE MYAPPROVALS_AUDIT_INSERT_RECORD(
  p_message_Type   IN myapprovals_audit.message_type%TYPE,
  p_component_Name IN myapprovals_audit.component_name%TYPE,
  p_username       IN myapprovals_audit.USERNAME%TYPE,
  p_timestamp      IN varchar2,
  p_request_Number IN myapprovals_audit.request_number%TYPE,
  p_module_Name    IN myapprovals_audit.module_name%TYPE,
  p_process_Name   IN myapprovals_audit.process_name%TYPE,
  p_version        IN myapprovals_audit.version%TYPE,
  p_task           IN myapprovals_audit.task%TYPE,
  p_error_Code     IN myapprovals_audit.error_code%TYPE,
  p_error_Message  IN myapprovals_audit.error_message%TYPE,
  p_message        IN myapprovals_audit.message%TYPE
)
IS
BEGIN
  INSERT INTO MYAPPROVALS_AUDIT (
    MESSAGE_TYPE,       -- You do not need to quote identifiers.
    COMPONENT_NAME,
    USERNAME,
    TIMESTAMP,
    REQUEST_NUMBER,
    MODULE_NAME,
    PROCESS_NAME,
    VERSION,
    TASK,
    ERROR_CODE,
    ERROR_MESSAGE,
    MESSAGE
  ) VALUES (
    p_message_Type,
    p_component_Name,
    p_username,
    TO_TIMESTAMP_TZ(                       -- Assuming TIMESTAMP WITH TIME ZONE
      p_timestamp,
      'YYYY-MM-DD"T"HH24:MI:SS.FF TZH:TZM' -- Your timestamp format model
    ) at time zone 'UTC',
    p_request_Number,
    p_module_Name,
    p_process_name,
    p_version,
    p_task,
    p_error_Code,
    p_error_Message,
    p_message
  );

  -- Do not COMMIT in the procedure.
  -- COMMIT when you finalise the transaction that way you can chain multiple
  -- procedures together and if one fails ROLLBACK the entire transaction.
END;
/

If it is in a different format then change the format model to suit your data.

I have stored procedure created in the database, in my C# code I want to convert the insert query into that stored procedure

You need something like this - and I would not really return the whole inserted row from the stored procedure (since you've already passed in all the input parameter values anyway) - just read the return new ID value from your OUTPUT parameter.

// set up your connection and command
using (var con = new SqlConnection(.....))
using (var cmd = new SqlCommand("dbo.InsertPhotoContest", con))
{
    // define it as stored procedure
    cmd.CommandType = CommandType.StoredProcedure;
    
    // add input parameters and set their value
    cmd.Parameters.Add("@contestTitle", SqlDbType.VarChar, 500).Value = photo.contestTitle;
    cmd.Parameters.Add("@contestLocation", SqlDbType.VarChar, 500).Value = photo.contestLocation;
    // and so forth, for all 11 input parameters
    
    // separate output parameter
    cmd.Parameters.Add("@PhotoContestID", SqlDbType.Int).Direction = ParameterDirection.Output;
    
    // open connection, execute stored procedure, close connection
    con.Open();
    
    cmd.ExecuteNonQuery();

    // get newly assigned ID        
    int newPhotoContestID = Convert.ToInt32(cmd.Parameters["@PhotoContestID"].Value);
    
    con.Close();
}

---

Related Topics