SQL Scripts

sridevi · May 14th, 2004, 07:37 AM

Hi all,
I need a manual script to insert data in a table.
i have to enter the data as input for each time i wish to execute the script. also if i enter the data, some have to concatinate with some static text. eg i have to store as <name> input for name </name> in a column. i need this for sql query analyzer for ms sql server.
Help me plz. I am new to sql.:(

happygv · May 14th, 2004, 10:20 AM

I am not not sure what is that you are looking for.

Where is your INPUT_FOR_NAME values stored now?
Are you looking for some automated code that picks the NAMES from where you have and format it as "<name> input for name </name>" and inserts in to Database?

Please explain on that so as to be more helpful to you.

Cheers!

-Vijay G

sridevi · May 15th, 2004, 01:09 AM

Hi actually the need is, i will be entering the input to enter into the dat sa i am givin name as xxx.
i wish to update in the table as <name>xxx</name>.
this xxx is not stable.
i will be giving a new name each time of executing the script. so i need a facility that the user will give the input seperately and have to embed in the query.

happygv · May 15th, 2004, 08:20 AM

Are you looking for something like a stored procedure or is that you are going to do this from ASP sort of front end?

Anyways let me give you that in the form of stored procedure.

Code:

Create Procedure sp_FormatName(@strName varchar(50))
as
    Declare @strSql Nvarchar(500)

    select @strSql='Insert MyTable values(''<Name>' + @strName + '</Name>'')'
    exec(@strSql)
Return
go

Once the procedure is created successfully (The command(s) completed successfully.), you can run this procedure by

Code:

Execute sp_FormatName "YOUR name"
go

Then you select records from your table

Code:

Select * from MyTable
Go

You should see this...

<Name>YOUR Name</Name>

Cheers!

-Vijay G

sridevi · May 16th, 2004, 11:34 PM

Hi vijay thanks a lot for your reply.
the problem i am getting now is i am getting updated as "<name>@strName</Name>" in the table, instead with the value of @strName

happygv · May 18th, 2004, 10:41 PM

Hi Sridevi,

That should work fine.

Code:

drop procedure sp_FormatName
go

Create Procedure sp_FormatName(@strName varchar(50))
as
    Declare @strSql Nvarchar(500)

    select @strSql='Insert MyTable values(''<Name>' + @strName + '</Name>'')'
    print @strSql
    --exec(@strSql)
Return
go

Execute sp_FormatName "Vijay"
go

I tried this and got the @strSql printed as
Insert MyTable values('<Name>Vijay</Name>')

You should be missing something else. Can you post the code of the procedure that you used at your end by giving the following?

sp_helptext <PROCEDURENAME>
go

Cheers!

-Vijay G

sridevi · May 18th, 2004, 11:47 PM

Hi vijay, thank you i got sorted out with that problem.
can you tell me how to add a integer value in the same query (for a different column)

happygv · May 19th, 2004, 01:33 AM

Hi Sridevi,

To add another number value, that particular line should be as follows

Code:

select @strSql='Insert MyTable(NAME_Column, NUMBER_Column) values(''<Name>' + @strName + '</Name>'',' + @intNumber + ')'

Add the @intNumber in the procedure parameter list as follows.

Code:

Create Procedure sp_FormatName(@strName varchar(50), @intNumber int)

BTW, how did you sort out the problem that you mentioned in your previous post. Can you post about that?

Hope that Helps.
Cheers!

-Vijay G

sridevi · May 20th, 2004, 12:11 AM

Hi vijay,

the problem is i missed the + for string concatination. Thank you for your tip.