How to insert dynamic regional text into a table in SQL Server using the stored procedure parameters

Question

I know how to insert different regional text into database in SQL Server using the normal insert statement as below:

INSERT INTO Telugu (Title, Description, [Image])
VALUES (N'వారెవ్వా.. టాయిలెట్ కోసం ఎంతపని చేశారు ', N'వారెవ్వా.. టాయిలెట్ కోసం ఎంతపని చేశారు', '\Images\templatemo_image_02.jpg')

But I want the same operation to performed using stored procedure using the parameters. Can anyone suggest how I can do this?

Are you asking how to create a procedure that takes three NVARCHAR parameters? e.g. `CREATE PROC X @input1 NVARCHAR(MAX), @input2 NVARCHAR(MAX), @input3 NVARCHAR(MAX) AS BEGIN INSERT tblName (col1, col2, col3) VALUES (@input1, @input2, @input3) END`? — ZLK, Jun 10 '16 at 04:28
No I am asking how to insert different Regional text using the Stored Procedure Parameters. Hope you understood my Question. If not tell me I'll post it much more briefly — Nihar, Jun 10 '16 at 04:35
Just make sure the parameter's datatype is set to `NVARCHAR(n)` — marc_s, Jun 10 '16 at 04:35
I am using the same datatype as you have specified Marc. But my question is how to insert the regional text using a Store Procedure. How should I use N in Stored Procedure for Inserting the text. — Nihar, Jun 10 '16 at 04:51
If you parameters **are** of type `NVARCHAR(n)`, then you already **have** Unicode text, so there's no need to add a `N` prefix anywhere. When you **call** the stored procedure, you must make sure to use the `N` prefix on any **string literal** in your T-SQL code — marc_s, Jun 10 '16 at 05:20

Arulkumar · Answer 1 · 2016-06-10T06:02:24.570

0

Hope you want to insert the values with N. That can be achieve by dynamic query.

CREATE PROCEDURE InsertRegionalText (
    @TitleText          NVARCHAR(MAX), 
    @DescriptionText    NVARCHAR(MAX), 
    @ImagePath          NVARCHAR(MAX) 
)
AS 
BEGIN 

    SET NOCOUNT ON;
    DECLARE @SqlQuery AS NVARCHAR(MAX) = '';

    SELECT @SqlQuery = N'
    INSERT INTO Telugu (Title, Description, [Image])
    VALUES (N''' + @TitleText + ', N''' + @DescriptionText + ', ' + @ImagePath + ');'

    --PRINT @SqlQuery

    EXEC (@SqlQuery)

END

edited Jun 10 '16 at 06:02

answered Jun 10 '16 at 05:06

Arulkumar

12,966
14
47
68

1

Curious to know why you preferred dynamic query over simple insert statement. – Swapnil Jun 10 '16 at 05:18
Thanks Arulkumar When I am trying to execute the Stored Procedure I am getting an Error: Incorrect syntax near '?'. Can you please help me here – Nihar Jun 10 '16 at 05:25
I have tried even the edited Code, it is also showing the Error: Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '?'. – Nihar Jun 10 '16 at 06:10
Yes, I too faced the issue, the `PRINT` returns `VALUES (N'????????....`. That causing me to didn't reply. Have you checked this [post answers](http://stackoverflow.com/q/761036/2451726) – Arulkumar Jun 10 '16 at 06:12
yeah i have check that but din't find anything that's helpful for me – Nihar Jun 10 '16 at 06:22

score 0 · Answer 2 · edited Jun 10 '16 at 11:09

Thanks Every one for the Help.

I was able to solve the Issue.

If our table Columns datatype is Nvarchar then we don't need to prefix any 'N' Character to our stored Procedure Parameters. I have added my tables Image below

enter image description here

Here is the stored Procedure which I have used:

CREATE PROCEDURE [dbo].[usp_UpdateSubCategoryDetails] 
-- Add the parameters for the stored procedure here
@SubCategoryID INT,
@SCDetailsHeading NVARCHAR(300),
@SCDetailsDescription NVARCHAR(3000),
@IMAGE NVARCHAR(100)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

    INSERT INTO tblSubCategoryDetails(SubCategoryID,SCDetailsHeading,SCDetailsDescription,[Image],IsActive)  
    VALUES(@SubCategoryID,@SCDetailsHeading,@SCDetailsDescription,@IMAGE,1) 

    SELECT SubCategoryID,SCDetailsHeading,SCDetailsDescription,[Image] FROM tblSubCategoryDetails
    WHERE IsActive =1

END

OUTPUT: 1 ఫోటో స్టోరి: 40 లోనూ అదే మాయ ఫోటో స్టోరి: 40 లోనూ అదే మాయ ../images/FilmyImages/heroine_raashi_khanna-1920x1080.jpg

How to insert dynamic regional text into a table in SQL Server using the stored procedure parameters

2 Answers2