Stored procedure from MYSQL to SqlServer

Question

Stored procedure from MYSQL to SqlServer

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

Create a stored procedure in SqlServer , I'm used to using MYSQL but now I have to do a stored procedure that inserts values but that the code id increases every time data is entered this would be in MYSQL :

delimiter
create procedure insert_tienda
(xnombtien varchar(50),
xobsvtien varchar(50))
begin
select count(*) into @num from tienda;
set @idtienda=concat('T',right(concat('000',cast((@num +1)as char)),4));
insert into tienda values(@idtienda,xnombtien,xobsvtien,1);
commit;
end;

But I do not know how to do it in SQLSERVER

sql-server

asked by Paul Julio 04.05.2017 в 07:02

source

2 answers

0

Just do the following:

When creating the table in SQL Server, in the properties of the field id-codigo indicate in the Specification of Identity the increment that you want for this field. With that you get a self-incremental field.

answered by 04.05.2017 в 08:24

Problem when making inquiries in psycopg2 How can I merge a background image with a color in an ANDROID layout?

score 1 · Accepted Answer

The equivalent in SQL Server would be:

CREATE PROCEDURE insert_tienda 
    @xnombtien varchar(50), @xobsvtien varchar(50)
AS
BEGIN
    SET NOCOUNT ON;

DECLARE @num int;

SELECT @num = COUNT(*) FROM tienda

INSERT INTO tienda VALUES ('T' + right('000' + cast(@num + 1 as varchar(3)), 3)
            , @xnombtien, @xobsvtien, 1)    
END
GO

and to call it you would not have to use more:

EXEC insert_tienda 'tienda1', 'observaciones tienda 1';

I guess you're aware that with this stored procedure:

You can not have more than 999 different identifiers
If you delete an existing record you will get repeated identifiers