help me to write procedure
-
Hi all, I wanna write 2 procedure 1) is when I execute it with parameter its give me the records of that particular table e.g. EXEC sp_Showrecords tablename 2) is when I execute it shown be all tables when I enter datbase name as parameter e.g EXEC sp_ShowTableaList dbname Can anybody help me.
hi, i don´t understand want you want:confused: Maybe i can help you there What kind of database you have and what do you want your procedure to do? :confused: Didn´t you found anything here in codeproject? Do you want do match/join contents from diferent tables and display it? try inner join in search code project.
-
hi, i don´t understand want you want:confused: Maybe i can help you there What kind of database you have and what do you want your procedure to do? :confused: Didn´t you found anything here in codeproject? Do you want do match/join contents from diferent tables and display it? try inner join in search code project.
I am using SQL server. e.g.
Create procedure sp_Fetchrecords @tbname sysname=NULL AS Select * from @tbname gogives me error -
I am using SQL server. e.g.
Create procedure sp_Fetchrecords @tbname sysname=NULL AS Select * from @tbname gogives me errorcheck the store procedures folder if it is already there! use alter procedure to change it. i usualy set the database on which i write on sql server up-left corner combobox. i don´t not use code but you can do that, do you need to code that? create procedures like this (with inner join) --------------sp----------- create procedure vendas_criadas @data_ini as smalldatetime, @data_fim as smalldatetime as begin select ve.id_venda as 'ID Venda',al.nome as 'Album',cl.primeiro_nome+' '+cl.ultimo_nome as 'Cliente',ut1.primeiro_nome+' '+ut1.ultimo_nome as 'Criador',ve.id_utilizador_criador_data as 'Data Criação' from dbo.vendas as ve inner join dbo.albuns as al on ve.id_album = al.id_album inner join dbo.clientes as cl on ve.id_cliente = cl.id_cliente inner join dbo.SPY_utilizadores as ut1 on ve.id_utilizador_criador = ut1.id_utilizador where ve.data_registo between @data_ini and @data_fim end go -------------execute------------------- exec vendas_criadas @data_ini = '1/1/2007', @data_fim = '1/1/2008'
-
check the store procedures folder if it is already there! use alter procedure to change it. i usualy set the database on which i write on sql server up-left corner combobox. i don´t not use code but you can do that, do you need to code that? create procedures like this (with inner join) --------------sp----------- create procedure vendas_criadas @data_ini as smalldatetime, @data_fim as smalldatetime as begin select ve.id_venda as 'ID Venda',al.nome as 'Album',cl.primeiro_nome+' '+cl.ultimo_nome as 'Cliente',ut1.primeiro_nome+' '+ut1.ultimo_nome as 'Criador',ve.id_utilizador_criador_data as 'Data Criação' from dbo.vendas as ve inner join dbo.albuns as al on ve.id_album = al.id_album inner join dbo.clientes as cl on ve.id_cliente = cl.id_cliente inner join dbo.SPY_utilizadores as ut1 on ve.id_utilizador_criador = ut1.id_utilizador where ve.data_registo between @data_ini and @data_fim end go -------------execute------------------- exec vendas_criadas @data_ini = '1/1/2007', @data_fim = '1/1/2008'
this answer is not related with my question . like simple select * statement inside procedure I didn't get your answer
-
Hi all, I wanna write 2 procedure 1) is when I execute it with parameter its give me the records of that particular table e.g. EXEC sp_Showrecords tablename 2) is when I execute it shown be all tables when I enter datbase name as parameter e.g EXEC sp_ShowTableaList dbname Can anybody help me.
Sorry if i can´t help you there, i sure more advanced programmer will help. Good luck
-
I am using SQL server. e.g.
Create procedure sp_Fetchrecords @tbname sysname=NULL AS Select * from @tbname gogives me error -
Create procedure sp_Fetchrecords
@tbname sysname=NULL
AS
SET NOCOUNT ON
IF @tbname IS NOT NULL
BEGIN
DECLARE @cmd NVARCHAR(4000)
SET @cmd = 'Select * from '+@tbname
EXEC(@cmd)
END
gothanks can you explain me why we have to execute the set command in procedure and one more question what is dynamic procedure and why should we have to use it. many thanks for your help:rose:
-
Sorry if i can´t help you there, i sure more advanced programmer will help. Good luck
thanks :rose:
-
thanks can you explain me why we have to execute the set command in procedure and one more question what is dynamic procedure and why should we have to use it. many thanks for your help:rose:
amistry_petlad wrote:
explain me why we have to execute the set command in procedure
Here is an explanation for SET NOCOUNT ON[^].....
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
amistry_petlad wrote:
explain me why we have to execute the set command in procedure
Here is an explanation for SET NOCOUNT ON[^].....
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
nO SORRY set is for SET @cmd
-
nO SORRY set is for SET @cmd
:-O I just realized there was the second one. The second one sets a variable that is the sql string, and the
exec(...)executes the sql string. You could get really fancy with this stored proc, such as addingwhereclause, andorder byas parameters into the stored proc, and add them to the@cmdvariable."The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon