I have a table in database !
I want to generate store procedure from that table by using tool of SQL server 2000( which include some statements : insert,delete, .....)
how can i do that ? thank you very much .
I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:
I want to create a table and to insert the result of the store procedure to it.
This is the code: Pay attention to the underlined sentence!
ALTER PROCEDURE [dbo].[test]
AS
BEGIN
SET NOCOUNT ON;
DROP TABLE tbl1
CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )
INSERT INTO tbl1 (first_name,last_name)
VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')
END
PLEASE HELP!!!! and God will repay you in kind!
Thanks!
Stored procedure P_GENERATE_PASSWORDS returns a list of randomly generated passwords designed to meet typical password complexity requirements of a minimum of 8 characters, with at least one each of uppercase letters, lowercase letters, numbers, and special characters. It can generate from 1 to 10,000 passwords as a result set.
The passwords are meant to be somewhat mnemonic by generating syllables consisting of an uppercase consonant, followed by a lower case vowel, and a lowercase consonant. A single number or special character separates syllables, except in the case of 2 syllables. If there are only 2 syllables, they will be separated by a number and a special character.
Input parameters @SYLLABLE_COUNT and @PASSWORD_COUNT determine the password length and the number of passwords.
if objectproperty(object_id('dbo.P_GENERATE_PASSWORDS'),'IsProcedure') = 1
begin drop procedure dbo.P_GENERATE_PASSWORDS end
go
create procedure dbo.P_GENERATE_PASSWORDS
(
@SYLLABLE_COUNTint = null ,
@PASSWORD_COUNTint= null ,
@PASSWORD_STRENGTHfloat= nulloutput
)
as
/*
Procedure Name: P_GENERATE_PASSWORDS
Procedure Description:
P_GENERATE_PASSWORDS returns a list of randomly generated passwords
designed to meet typical password complexity requirements of a minimum
of 8 characters, with at least one each of uppercase letters,
lowercase letters, numbers, and special characters.
The passwords are meant to be somewhat mnemonic by generating
syllables consisting of an uppercase consonant, followed by a
lower case vowel, and a lowercase consonant. Syllables are separated
by a single number or special character, except in the case of 2 syllables.
If there are only 2 syllables, the syllables will be separated by
a number and a special character.
Passwords can be from 2 to 8 syllables in length.
Input parameter @SYLLABLE_COUNT is the total syllables in each output password.
The value of @SYLLABLE_COUNT must be between 2 and 8. If it is < 2 or null,
it is set to 3. If it is > 8 it is set to 8.
Input parameter @PASSWORD_COUNT is the total passwords to be returned.
The value of @SYLLABLE_COUNT must be between 1 and 10,000.
If it is < 1, it is set to 1. If it is null, it is set to 10.
If it is > 10,000 it is set to 10,000.
Output parameter @PASSWORD_STRENGTH returns the total possible
passwords that are possible for the selected @SYLLABLE_COUNT.
*/
set nocount on
-- Set password syllable count
set @SYLLABLE_COUNT =
case
when @SYLLABLE_COUNT is null
then 3
when @SYLLABLE_COUNT < 2
then 3
when @SYLLABLE_COUNT > 8
then 8
else @SYLLABLE_COUNT
end
-- Set password count
set @PASSWORD_COUNT =
case
when @PASSWORD_COUNT is null
then 10
when @PASSWORD_COUNT < 1
then 1
when @PASSWORD_COUNT > 10000
then 10000
else @PASSWORD_COUNT
end
declare @con varchar(200)
declare @vowel varchar(200)
declare @special varchar(200)
declare @num varchar(200)
declare @special_only varchar(200)
declare @con_len int
declare @vowel_len int
declare @special_len int
declare @num_len int
declare @special_only_len int
declare @strings int
-- set character strings for password generation
select
@con= 'bcdfghjklmnpqrstvwxyz',
@vowel= 'aeiou',
@num= '1234567890',
@special_only= '~!@#$%^&*()_+-={}|[]:;<>?,./'
set @special = @num+@special_only
-- set string lengths
select@con_len= len(@con),
@vowel_len= len(@vowel),
@special_len= len(@special),
@num_len= len(@num),
@special_only_len= len(@special_only) ,
@strings =
case
when @SYLLABLE_COUNT < 3
then 2
else @SYLLABLE_COUNT-1
end
--select @con, @vowel, @special, @num, @special_only,
--SELECT @con_len, @vowel_len, @special_len, @num, @special_only_len, @strings
-- Declare number tables to generate rows
declare @num1 table (NUMBER int not null primary key clustered)
declare @num2 table (NUMBER int not null primary key clustered)
declare @num3 table (NUMBER int not null primary key clustered)
declare @rows_needed_root int
set @rows_needed_root = convert(int,ceiling(sqrt(@PASSWORD_COUNT)))
-- Load number 0 to 16
insert into @num1 (NUMBER)
select 0 union all select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7 union all
select 8 union all select 9
order by 1
-- Load table with numbers zero thru square root of the number of rows needed +1
insert into @num2 (NUMBER)
select
NUMBER = a.NUMBER+(10*b.NUMBER)
from
@num1 a cross join @num1 b
where
a.NUMBER+(10*b.NUMBER) <
@rows_needed_root
order by
1
-- Load table with the number of passwords needed
insert into @num3 (NUMBER)
select
NUMBER = a.NUMBER+(@rows_needed_root*b.NUMBER)
from
@num2 a
cross join
@num2 b
where
a.NUMBER+(@rows_needed_root*b.NUMBER) < @PASSWORD_COUNT
order by
1
-- Declare password string table
declare @p table (
numberintnot null
primary key clustered,
m1 varchar(10)not null,
m2 varchar(10)not null,
m3 varchar(10)not null,
m4 varchar(10)not null,
m5 varchar(10)not null,
m6 varchar(10)not null,
m7 varchar(10)not null,
m8 varchar(10)not null,
s1 varchar(10)not null,
s2 varchar(10)not null,
s3 varchar(10)not null,
s4 varchar(10)not null,
s5 varchar(10)not null,
s6 varchar(10)not null,
s7 varchar(10)not null
)
insert into @p
select
NUMBER,
-- M1 through M8 will be syllables composed of a single randomly selected
-- uppercase consonant, a single randomly selected lowercase vowel,
-- followed by as single randomly selected lowercase consonant.
m1 =
upper(substring(@con, (R11%@con_len)+1,1))+
substring(@vowel,(R12%@vowel_len)+1,1)+
substring(@con, (R13%@con_len)+1,1),
m2 =
upper(substring(@con, (R21%@con_len)+1,1))+
substring(@vowel,(R22%@vowel_len)+1,1)+
substring(@con, (R23%@con_len)+1,1),
m3 =
upper(substring(@con, (R31%@con_len)+1,1))+
substring(@vowel,(R32%@vowel_len)+1,1)+
substring(@con, (R33%@con_len)+1,1),
m4 =
upper(substring(@con, (R41%@con_len)+1,1))+
substring(@vowel,(R42%@vowel_len)+1,1)+
substring(@con, (R43%@con_len)+1,1),
m5 =
upper(substring(@con, (R51%@con_len)+1,1))+
substring(@vowel,(R52%@vowel_len)+1,1)+
substring(@con, (R53%@con_len)+1,1),
m6 =
upper(substring(@con, (R61%@con_len)+1,1))+
substring(@vowel,(R62%@vowel_len)+1,1)+
substring(@con, (R63%@con_len)+1,1),
m7 =
upper(substring(@con, (R71%@con_len)+1,1))+
substring(@vowel,(R72%@vowel_len)+1,1)+
substring(@con, (R73%@con_len)+1,1),
m8 =
upper(substring(@con, (R81%@con_len)+1,1))+
substring(@vowel,(R82%@vowel_len)+1,1)+
substring(@con, (R83%@con_len)+1,1),
-- S1 through S7 will each be a single randomly selected
-- number or special character. At least one of the used
-- columns will be a number and one will be a special character.
s1 =
case
when NUMBER_COL = 1
then substring(@num,(RS1%@num_len)+1,1)
when SPECIAL_COL = 1
then substring(@special_only,(RS1%@special_only_len)+1,1)
else substring(@special,(RS1%@special_len)+1,1)
end,
s2 =
case
when NUMBER_COL = 2
then substring(@num,(RS2%@num_len)+1,1)
when SPECIAL_COL = 2
then substring(@special_only,(RS2%@special_only_len)+1,1)
else substring(@special,(RS2%@special_len)+1,1)
end,
s3 =
case
when NUMBER_COL = 3
then substring(@num,(RS3%@num_len)+1,1)
when SPECIAL_COL = 3
then substring(@special_only,(RS3%@special_only_len)+1,1)
else substring(@special,(RS3%@special_len)+1,1)
end,
s4 =
case
when NUMBER_COL = 4
then substring(@num,(RS4%@num_len)+1,1)
when SPECIAL_COL = 4
then substring(@special_only,(RS4%@special_only_len)+1,1)
else substring(@special,(RS4%@special_len)+1,1)
end,
s5 =
case
when NUMBER_COL = 5
then substring(@num,(RS5%@num_len)+1,1)
when SPECIAL_COL = 5
then substring(@special_only,(RS5%@special_only_len)+1,1)
else substring(@special,(RS5%@special_len)+1,1)
end,
s6 =
case
when NUMBER_COL = 6
then substring(@num,(RS6%@num_len)+1,1)
when SPECIAL_COL = 6
then substring(@special_only,(RS6%@special_only_len)+1,1)
else substring(@special,(RS6%@special_len)+1,1)
end,
s7 =
case
when NUMBER_COL = 7
then substring(@num,(RS7%@num_len)+1,1)
when SPECIAL_COL = 7
then substring(@special_only,(RS7%@special_only_len)+1,1)
else substring(@special,(RS7%@special_len)+1,1)
end
from
(
select
aaaa.*,
-- Select random columns numbers to force at least
-- one special character and one number character
-- in each password
NUMBER_COL = (X1%@strings)+1 ,
SPECIAL_COL = ((((X2%(@strings-1))+1)+X1)%@strings)+1
from
(
select top 100 percent
NUMBER,
-- Generate random numbers for password generation
R11 = abs(convert(bigint,convert(varbinary(20),newid()))),
R12 = abs(convert(bigint,convert(varbinary(20),newid()))),
R13 = abs(convert(bigint,convert(varbinary(20),newid()))),
R21 = abs(convert(bigint,convert(varbinary(20),newid()))),
R22 = abs(convert(bigint,convert(varbinary(20),newid()))),
R23 = abs(convert(bigint,convert(varbinary(20),newid()))),
R31 = abs(convert(bigint,convert(varbinary(20),newid()))),
R32 = abs(convert(bigint,convert(varbinary(20),newid()))),
R33 = abs(convert(bigint,convert(varbinary(20),newid()))),
R41 = abs(convert(bigint,convert(varbinary(20),newid()))),
R42 = abs(convert(bigint,convert(varbinary(20),newid()))),
R43 = abs(convert(bigint,convert(varbinary(20),newid()))),
R51 = abs(convert(bigint,convert(varbinary(20),newid()))),
R52 = abs(convert(bigint,convert(varbinary(20),newid()))),
R53 = abs(convert(bigint,convert(varbinary(20),newid()))),
R61 = abs(convert(bigint,convert(varbinary(20),newid()))),
R62 = abs(convert(bigint,convert(varbinary(20),newid()))),
R63 = abs(convert(bigint,convert(varbinary(20),newid()))),
R71 = abs(convert(bigint,convert(varbinary(20),newid()))),
R72 = abs(convert(bigint,convert(varbinary(20),newid()))),
R73 = abs(convert(bigint,convert(varbinary(20),newid()))),
R81 = abs(convert(bigint,convert(varbinary(20),newid()))),
R82 = abs(convert(bigint,convert(varbinary(20),newid()))),
R83 = abs(convert(bigint,convert(varbinary(20),newid()))),
RS1 = abs(convert(bigint,convert(varbinary(20),newid()))),
RS2 = abs(convert(bigint,convert(varbinary(20),newid()))),
RS3 = abs(convert(bigint,convert(varbinary(20),newid()))),
RS4 = abs(convert(bigint,convert(varbinary(20),newid()))),
RS5 = abs(convert(bigint,convert(varbinary(20),newid()))),
RS6 = abs(convert(bigint,convert(varbinary(20),newid()))),
RS7 = abs(convert(bigint,convert(varbinary(20),newid()))),
X1 = convert(bigint,abs(convert(int,convert(varbinary(20),newid())))),
X2 = convert(bigint,abs(convert(int,convert(varbinary(20),newid()))))
from
@num3 aaaaa
order by
aaaaa.NUMBER
) aaaa ) aaa
order by
aaa.NUMBER
-- Compute password strength as the total possible passwords
-- for the selected number of syllables.
select
@PASSWORD_STRENGTH =
power((@con_len*@con_len*@vowel_len)*1E,@SYLLABLE_COUNT*1E)*
(@special_only_len*@num_len*1E)*
case
when @strings < 3
then 1E
else power(@special_len*1E,(@strings-2)*1E)
end
-- Declare output table
declare @PASSWORD table
(
NUMBER intnot null
identity(1,1) primary key clustered,
[PASSWORD]varchar(32)not null
)
insert into @password ([PASSWORD])
selecttop 100 percent
[PASSWORD]
from
(
select
distinct
[PASSWORD] =
convert(varchar(32),
case
when @SYLLABLE_COUNT = 2
then m1+s1+s2+m2
else
substring(m1+s1+m2+s2+m3+s3+m4+s4+m5+s5+m6+s6+m7+s7+m8
,1,(@SYLLABLE_COUNT*4)-1)
end)
from @P
) a
where
-- Verify at least one number in password
[PASSWORD] like '%[1234567890]%'and
-- Verify at least one special character in password
[PASSWORD] like '%[^a-z1234567890]%'
order by
newid()
select * from @password order by NUMBER
return 0
go
grant execute on dbo.P_GENERATE_PASSWORDS to public
go
-- Test Script
declare @SYLLABLE_COUNTint
declare @PASSWORD_COUNTint
declare @PASSWORD_STRENGTHfloat
select @SYLLABLE_COUNT = 2 , @PASSWORD_COUNT = 5
print '@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+
', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)
exec dbo.P_GENERATE_PASSWORDS
@SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output
print '@PASSWORD_STRENGTH = '+convert(varchar(50),@PASSWORD_STRENGTH)
print ''
select @SYLLABLE_COUNT = 3 , @PASSWORD_COUNT = 6
print '@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+
', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)
exec dbo.P_GENERATE_PASSWORDS
@SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output
print '@PASSWORD_STRENGTH = '+convert(varchar(50),@PASSWORD_STRENGTH)
print ''
select @SYLLABLE_COUNT = 5 , @PASSWORD_COUNT = 7
print '@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+
', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)
exec dbo.P_GENERATE_PASSWORDS
@SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output
print '@PASSWORD_STRENGTH = '+convert(varchar(50),@PASSWORD_STRENGTH)
print ''
select @SYLLABLE_COUNT = 8 , @PASSWORD_COUNT = 20
print '@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+
', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)
exec dbo.P_GENERATE_PASSWORDS
@SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output
print '@PASSWORD_STRENGTH = '+convert(varchar(50),@PASSWORD_STRENGTH)
print ''
Results of Test Script:
@SYLLABLE_COUNT = 2, @PASSWORD_COUNT = 5
NUMBER PASSWORD
----------- --------------------------------
1 Tis|2Fun
2 Miy5]Fib
3 Bay1|Puz
4 Tel3.Pus
5 Duq0@Roy
@PASSWORD_STRENGTH = 1.40999e+009
@SYLLABLE_COUNT = 3, @PASSWORD_COUNT = 6
NUMBER PASSWORD
----------- --------------------------------
1 Qab@Kaz0Lan
2 Sav1Tig]Hat
3 Pah6Fic|Cic
4 Buz7Viz=Mec
5 Vig^Wah9Xuf
6 Qew2Mif^Mix
@PASSWORD_STRENGTH = 3.10902e+012
@SYLLABLE_COUNT = 5, @PASSWORD_COUNT = 7
NUMBER PASSWORD
----------- --------------------------------
1 Mux4Zor_Jog{Vec,Bih
2 Ker1Qem[Gat,Hut|Zif
3 Red}Ciq5Ber%Son:Qej
4 Cov@Doz8ZowFic>Pos
5 Tad0Bek&Fug_Kiv9Rez
6 Pil1Nul$Vil~Koh_Xel
7 Zuk4Gir&Yep|Ned)Sap
@PASSWORD_STRENGTH = 2.29917e+022
@SYLLABLE_COUNT = 8, @PASSWORD_COUNT = 20
NUMBER PASSWORD
----------- --------------------------------
1 Biz&Xak9Gew{Vuf[Tix;Qap-Bik{Vay
2 Rof<Job*Fax-Niq/Zew9Pah:Bag(Zok
3 Noh1Nor7Rul5Fon@Mig>Xod.Lay.Maq
4 Piw:Keb}Rod8Yah}VawLet@Yoq9Sav
5 Hav@Qer/Met7Zig&Jiw4Pot-Fod(Zat
6 Bid_Lal+Bay3Fos9FezFaw!Kad4Zok
7 Qar-Kig-Lem3Yeq?Xuj7Zun,Xid=Xel
8 Biq6Jot:Caj(Xun2Kup[Fax|Gec,Xon
9 Yac7Nox^Woy~Wag0XanHil3Cab/Nit
10 Pod+Kor%Fov7Vil,Dor:Xoq!Kel3Poq
11 Goc)Roz7Ruq/Pad8Jeh*Xaj&Dew{Duy
12 Sik/Ruj@Wiv9Qik[Sub=Qim,Ned:Qit
13 Les9Har&Ceb5Heg^Fov0Vaf1Fuf[Maq
14 Deg6Yiw$Peg:Wuj7Woc_Mip|Kam9Zus
15 Nix^Dev%Qoj=Seq[Jig6Lig}Day-Ric
16 Dux;Woy=Zud1Mak5Yej$Kav2Mek5Buh
17 Yuv8Mor9Wix&Giq5Zar@Nuk$Pey<Lok
18 Dem~Kof-Yoq(Xig$TewFun7Meq2Kik
19 Caq1Qag{Pes{Gex|Til=Vuk7Tig1Vur
20 Miw)Law}Tun2Lop.Jix#Riq|Yat$Juc
@PASSWORD_STRENGTH = 1.46214e+037
CODO ERGO SUM
My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure
exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'
I am getting below error :
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.
In SQL 2000 version, we can select all the Stored Procedure function and export out all the script.
But in 2005 version, how it can be done?
Thanks
I need to develop some crystal reports on some .NET ado datasets.This is easy to do if I actually had data to work with. It is mucheaser creating reports with you have data.Therefore, I would like to run the stored procedure that the .NET adodataset is built on and generate an XML file. I could run the projectand try to figure out where the developers are making the call to thestored procedure and insert a line to writetoxmlfile. I would rathernot have to mess with their code.Is there a way working with SQL Server (either query analyzer orenterprise manager, dts, or whatever) that I can generate an xml file.I see that I can run a stored procedure and get an xml style return inquery analyzer, but I don't know how to save that as an actual file.Thanks for the help.Tony
View 3 Replies View RelatedHi All,
I know we could get the script of a stored procedure in Enterprise Manager. However I need to do this using a utility in dos prompt. I need to specity a stored procedure and script out to an output file.
Is this possible?
Thank you!
I need creating a store procedure which generates custom IDs for each asset. I am programming for Fixed Assets in VB6 with SQL Server 2005. For example, when a new Asset is added ,I need to auto generate the ID based on existing IDs. New ID should not exist in tblAssets table.
Table Name : tblAssets
Fields : AssetID > Int,Primary Key,this is internal ID (identity seed)
AssetExtID >nvarchar(50),this is external ID, need to generate/user entered.
Below is the example of data in tblAssets :
AssetID AssetExtID ProjectID ItemName Qty UOM UnitCost .....
1 PROSP-00001 PROSPERITY SPLIT-AC 2 NOS $200
2 PROSP-00002 PROSPERITY LAPTOP 1 NOS $500
3 UNIII-00001 UNION III LAPTOP 5 NOS $400
4 UNIII-00002 UNION III RECEIVER 2 NOS $312
The AssetExtID depends on the ProjectID which is in tblProjects.
I will take care of the first 5 characters to generate. But the number part I need to generate by checking existing data. The AssetExtID should not be duplicate. Its unique for each asset.
We need to create a pdf file from SQL server preferably from a stored procedure. Application will call the stored procedure and it should generate pdf. From my research it appears it can be done using various external tools with licensing/costs. But is it possible to do this within sql server database without additional costs? I read that this can be done by SSRS in SQL server but not sure if it is a good solution and if it is additional licensing..
View 3 Replies View RelatedI have a need to do the following:
Generate a Stored Procedure and have the output written in a csv format.
I have everything I need to capture the data via stored procedure, but I am lost on a way to 'INSERT' the data values into a csv file.
This stored procedure will be triggered by another application.
Could someone please help.
thanks
Dear everyone,
I would like to create auto-generated "string" ID for any new record inserted in SQL Server 2000.
I have found some SQL Server 2000 book. But it does not cover how to create procedure or trigger to generate auto ID in the string format.
Could anyone know how to do that?? Thanks!!
From,
Roy
There is SCPTXFR.EXE script that has an option to create a script for all DB objects in one file or create separate files for each DB object - one for tables, one for SP-s, one for triggers, etc.
Is there a way to generate:
1. Separate script for each stored procedure (not from Enterprise Manager but automatically from command line or otherwise);
2. Script all DB objects excluding stored procedures in one file.
Thank you.
Dear experts,
My MS SQL Server 2005 is generating the following error. may i know what's wrong with it?
"
The Collect Procedure for the "DTSPipeline" service in DLL "XXX:Program FilesMicrosoft SQL Server (x86)90DTSBinnDTSPipelinePerf.dll" generated an exception or returned an invalid status. Performance data returned by counter DLL will be not be returned in Perf Data Block. The exception or status code returned is the first DWORD in the attached data.
"
Thanks in advance for any assistance rendered.
pat
Hi guysi would really appreciate your help here. what am i doing wrong with this stored procedure ALTER PROCEDURE usp_CreateNewCustomer( @LName as varchar(50), @FName as varchar(50), @Dept as varchar(50)=NULL, @PhoneType as varchar(50)=NULL, @Complete as Bit=NULL, @CustomerID as Int OUTPUT, @FaxModel as varchar(50)=NULL, @FaxNumber as varchar(50)=NULL, )ASINSERT INTO [CustomerInfo] ([LName], [FName], [Dept], [PhoneType], [Complete]) VALUES (@LName, @FName, @Dept, @PhoneType, @Complete)SELECT SCOPE_IDENTITY()INSERT INTO Extras (CustomerID, FaxModel, FaxNumber) VALUES (@CustomerID, @FaxModel, @FaxNumber)RETURN It keeps on complaning "'usp_CreateNewCustomer' expects parameter '@CustomerID', which was not supplied."thanks
View 4 Replies View RelatedHi all,
I have a few question regarding SPROC. Firstly, I want to create a sp, that will return a multiple column of data, how do you achieve that. Secondly, I want to create a store procedure that will return a multiple columns in a cursor as an output variable. Any help will be much appreciated. Can you have more then 1 return parameters???
Thanks.
Kabir
I have asp.net web application which interface with SQL server,
I want to run store procedure query of SQL using my asp.net application.
How to declare connectons strings, dataset, adapter etc to run my store procedure resides in sql server.
for Instance Dim connections as string,
Dim da as dataset, Dim adpt as dataadapter. etc
if possible , then show me completely code so I can run store procedure using my button click event in my asp.net application
thank you
maxmax
I am not sure if it is right place to ask the question. I have a store procedure in which funcitons are called and several temp tables are created. When I use sql analyzer, it runs fast. But when I called it from asp.net app, it runs slow and it waits and waits to get data and insert into temp tables and return. Though I have used with (nolock), it still not imprvoed. Any suggestions? Thanks in advance.
NetAdventure
Hello, I am very new to ASP.NET and SQL Server. I am a college student and just working on some project to self teaching myself them both. I am having a issue. I was wondering if anyone could help me.
Ok, I am creating a web app that will help a company to help with inventory. The employee's have cretin equipment assign to them (more then one equipment can be assigned). I have a search by equipment and employee, to search by equipment the entire database even then equipment that isn't assigned with the employee's it shows only that employees and if they have any equipment. They can select a recorded to edit or see the details of the record. The problem i am having is that the Info page will not load right. I am redirected the Serial Number and EmployeeID to the info page. I got everything working except one thing. If a person has more then one equipment when i click on the select it redirect and only shows the first record not the one I selected. and when i change it, a employee that doesn't have equipment will not show up totally.
SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier
FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNo
EmployeeID and SerialNo are primary keys.
I was thinking maybe create a store procedures, but I have no idea how to do it. I created this and no syntax error came up but it doesn't work
CREATE PROCEDURE dbo.inventoryinfo AS
DECLARE @EmployeeID int DECLARE @SerialNo varchar(50)
IF( @SerialNo is NULL)
SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier
FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID
ELSE
SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier
FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNoGO
Also, when a user selects the select button in either Employee search or Equipment search it redirects them to info.aspx. Should I create a different aspx page for both? But I don't believe I should do that. I don't know if I gave you enough information or not. But if you could give examples that would be wonderful! Thanks so much Nickie
I want to know when does SQL flushes Store Procedure from the cache.
Thank You,
Piyush Patel
Hi, All,
I need to write a sales report which needs to seperate total from direct sale and agentsale. The report looks like this( in the table of query,we have agentnumber, productname, sales, month
Month salefromagent directsale total
Jan 1100 2300 3400
Feb 800 500 1300
..........
Dec
---------------------------------
I know we can handle this in the program use two queries.
But is there a way to do it use store procedure and then pass the result of store procedure to the ASP program. I am trying to write my first store procedure, thanks for any clue.
Betty
Hi all,
I need to write a sales store procedure.
The sales summary is basically group by agentCode.
But the problem is some agents have subagents.
i.e., in the sales table.
one agent 123456 has many subagents whose agentCode start with 17. And I want to group sales for all subagents who have agentCode 17XXXX to agent 123456's sales.
what should I do in the store procedure.
Thanks
Betty.
I am trying to create a store procedure that look at a table and only maintain 30 worth of data only forever. The data that fall outside the 30 days need to be deleted. Can anyone help me with this?
Thanks
Lystra
I would like to create a employee store procedure to insert a record and mean while i can retrieve the EmployeeID for the record just inserted. Can you show me how to create a store procedure like this. Now i have a insert store procedure in the following:
CREATE PROCEDURE AddEmployee
(@efname nvarchar(20),
@elname nvarchar(20)
)
AS
insert into tblEmployeeName
(EmployeeFName, EmployeeLName)
values (@efname,@elname)
GO
Thanks.
I created a batch file to run a DTS saved as a STRUCTURED STORE FILE. Works fine on the command promt.
Can I use a SPROC to call something like a shell command to execute that batch file? If yes, how is this be possible?
Thanks
This is an Oracle store procedure. Can Any body help me to convert it into SQL Server stored Procedure
PROCEDURE CALC_PERC (DB_ID IN NUMBER, LAT_TYPE IN CHAR) IS
Tot_work_all number(12,2);
Bid_tot number(12,2);
Ewo number(12,2);
Overruns number(12,2);
Underruns number(12,2);
Contr_tot_all number(12,2);
sContractType ae_contract.contr_type%type;
BEGIN
select sum(nvl(tamt_ret_item,0) + nvl(tamt_paid_item,0))
into Tot_work_all
from valid_item
Where db_contract = db_id;
Select sum(Contq * Contr_Price) into Bid_tot
From Valid_item
Where nvl(New_Item,'N') <> 'Y'
and db_contract = db_id;
Select sum(Qtd * Contr_price) into Ewo
From Valid_item
Where nvl(New_item,'N') = 'Y'
and db_contract = db_id;
Select Sum((Qtd-Nvl(Projq,0))*Contr_Price) into Overruns
From Valid_item
Where Qtd > Nvl(Projq,0)
and db_contract = db_id
and nvl(New_Item,'N') = 'N';
IF LAT_type <> 'R' THEN
Select Sum((Nvl(Projq,0)-Contq) * Contr_Price) into Underruns
From Valid_item
Where Nvl(Projq,0) < Contq
and db_contract = db_id
and nvl(New_Item,'N') = 'N';
ELSE
Select Sum((Nvl(Qtd,0)-Contq) * Contr_Price) into Underruns
From Valid_item
Where Nvl(Qtd,0) < Contq
and db_contract = db_id
and nvl(New_Item,0) = 'N';
end if;
Contr_tot_all:= NVL(Bid_tot,0) +NVL(ewo,0) +NVL(overruns,0)
+NVL(underruns,0);
IF Contr_tot_all = 0 THEN
Select Contr_type into sContractType from ae_contract where db_contract = db_id;
IF sContractType = 'A' OR sContractType = 'T' THEN
--If the divisor is zero here, it's not an error.
update ae_contract set perc_compu = 0 where db_contract = db_id;
ELSE
--If the divisor is zero here, it would be an error
update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
END IF;
Else
--Here we have a real number to calculate, so go ahead and do your stuff!
update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
END IF;
END;
1. can a stored procedure call itself? if else how ?>
2. how to run a job for a stored procedure to exucte every day.
hello
iam new to store procedures.can anyone tell me how do i put in this sql statement into store procedure
SELECT * FROM PortMaster WHERE PortCode= '" & Request.QueryString("PortCode") & "' "
TheProblem is coming in ,how do i put "Request.QueryString("PortCode")" into sql queranalyzer.iam using this in .aspx page.
depending on querystring the record is selected.
hello
iam using store procedure to open the the selected rows in datagrid into Excel.but iam getting blank spreadsheet.the querystrings are getting passed but the rows turn out to be blank.
if i do not use store procedure than everything is ok.the querystring contains multiple strings so as to display it in excel.
the stroe procedure is
CREATE PROCEDURE Employee_ExcelDisplayHO(@ID varchar(100))
AS
SELECT * FROM employee_master WHERE empuser_id IN (@ID)
and this is the statement that i use in my .aspxpage to call store procedure
Dim Cmd As New SqlDataAdapter("Employee_ExcelDisplayHO", myconn)
cmd.SelectCommand.CommandType = CommandType.StoredProcedure
cmd.SelectCommand.Parameters.Add(New SqlParameter("ID", SqlDbType.VarChar, 100))
cmd.SelectCommand.Parameters("@ID").Value = Request.QueryString("ID")
any suggestions wht may be wrong
iam using store procedure to return certain no. of columns frm table.the query is
SELECT empuser_id,FullName,CAddress,PAddress,PhResidence,Mobile FROM employee_master WHERE empuser_id IN (" & txtID.Text & ")
i return it to datagrid in my .aspx(using VB.net) page.but i am getting blank datagrid. when i write the above query in my codebehind it runs normally and datagrid is populated with records.
i tried this in query analyzer as well it runs normally returing rows.
only in store procedure it gives me blank record.
any suggestions what is wrong ?
hello sir
i want to write store procedure.
I write following code--
drop Proc Analyst
CREATE PROC Analyst
@UserID varchar(20),
@StockName varchar(50)
AS
INSERTtAnalystAssignHistory
(
AnalystAssignHistoryCode,
SecurityCode,
ISIN,
StockName,
UserID,
IsDummyEnabled,
AnalystAssignDate,
StatusCode,
CreatedBy,
CreateDate,
UpdatedBy,
UpdateDate
)
SELECTCOALESCE((SELECT MAX(AnalystAssignHistoryCode) FROM tAnalystAssignHistory), 0) + 1,
SecurityCode,
ISIN,
StockName,
'ShahVis',
IsDummyEnabled,
AnalystAssignDate,
'A',
'Gayatri',
GETDATE(),
NULL,
NULL
FROMtAnalystAssignHistory
WHEREUserId = @UserID
and StockName = @StockName
GO
EXEC Analyst'PatelAmi','Gujarat Ambuja Cements Ltd.'
In this procedure i want to runtime insert ShahVis i.e REassignuserid. it will always change so i want runtime insert this field.
and created by this is store in globaly i want to retrive this runtime.
Please help me.
I am trying to loop through a string of ID's and check if theproductname for the ID is 'Chai' if so change it to 'Tea'. This is inthe northwind db - products table. call it exec sp_UpdateProduct'1,2,3'It does not work, any ideas - syntax, etc....CREATE PROCEDURE dbo.sp_UpdateProduct@IDs varchar(200)ASBEGINdeclare @sStr1 varchar(200)declare @iStrLen1 intdeclare @sParseElm1 char(2)declare @sStatus varchar(200)declare @count intset @sStr1= @IDsset @iStrLen1= len(@sStr1)SET @count = 0while charindex(',',@sStr1) <> 0beginselect @sParseElm1 = substring(@sStr1,1,CHARINDEX(',',@sStr1 ) - 1)set @sStatus = 'select productname from products where productid='+@sParseElm1if @sStatus = 'CHAI'update products set productname = 'TEA'elseupdate products set productname = 'CHAI'set @sStr1 = substring(@sStr1, CHARINDEX(@sStr1,',') + 1, @iStrLen1)endendGO
View 1 Replies View Related
Hi,
I follow the example for store procedure from this link to deal with large amount of data and it work great.
http://www.4guysfromrolla.com/webtech/042606-1.shtml
CREATE PROCEDURE [dbo].[usp_PageResults_NAI]
(
@startRowIndex int,
@maximumRows int
)
AS
DECLARE @first_id int, @startRow int
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that
-- Get the first employeeID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID FROM employees ORDER BY employeeid
-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows
SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.EmployeeID
SET ROWCOUNT 0
GO
my problem is how do i do the Sort for this.
Any help would be appreciated.
Thanks
Ddee
Dear Frnds,
can anyone tell me is it possibel to use store procedure within the stored procedure ?? is it possible ??? if yes ?? then give me simple example
awaiting for positive reply
Thanks
Hiren