Parameters As Table?
Apr 25, 2007Is it possible to use parameters as table? @table maybe?
View 11 Replies
ADVERTISEMENT
Need Help W/ Postback To 'Customers' Table On Form Using Select Query From 'Parameters' Table
Dec 20, 2007I have set up a 'Parameters' table that solely stores all pre-assigned selection values for a webform. I customized a stored query to Select the values from the Parameters table. I set up the webform. The result is that the form1.apsx automatically populates each DropDownList task with the pre-assigned values from the 'Parameters' table (for example, the stored values in the 'Parameters' table 'Home', 'Business', and 'Other' populate the drop down list for 'Type').
The programming to move the selected data from form1.aspx to a new table in the SQL database perplexes me. If possible, I would like to use the form1.aspx to Postback (or Insert) the "selected" data to a *new* column in a *new* table (such as writing the data to the 'CustomerType' column in the 'Customers' table; I clearly do not want to write back to the 'Parameters' table). Any help to get over this hurdle would be deeply appreciated.
Parameters And Table Names
Sep 11, 2001Please help,
I am inserting data into a newly created table with many rows similar to thoes below using a sp with a parameter @Extract
CREATE PROCEDURE mytest
@Extract int
AS
DECLARE @MyName varchar(25)--these keep changing for each total
DECLARE @Counter varchar(2)--this will increment from 2 to 40
SELECT @MyName ='tblG3>74Ex' +convert(varchar(3), @Extract)
SET @total1 =(SELECT COUNT(*)
FROM @MyName
WHERE (sSurgery_id ="S0" + @Counter) and (iExtract_nm = @Extract)
SELECT @MyName ='tblG335-74Ex' +convert(varchar(3), @Extract)
SET @total2 = (SELECT COUNT(*)
FROM @MyName
WHERE (sSurgery_id ="S0" + @Counter)and (iExtract_nm =@Extract)
insert into tblNew([35-74],[>74],etc,etc,Report4)
values (@total1,@total2,ect,ect,@report4)
it all works until I try to use the @MyName
Please put me out of my misery
Many thanks
David
Using A Table As SProc Parameters
May 2, 2005Okay, here's the scenario....
I have a bunch of un-related tables that I need to make relational.
I have a bunch of SProcs set up to help with the relating of the tables but I need the quickest way to get the values from the tables and into the SProcs.
Currently, I'm using .NET to cycle through the necessary tables, sending in their values into the SProcs, one at a time.
I'd like to have some SQL-ized way to do this so I can just make another step in my DTS package that already copies over the un-related tables from DB2.
What I'd imagine the code to look like would be something like...
EXEC SP_EMPLOYEES (SELECT FIRST_NAME,LAST_NAME FROM oldEmployees)
I know, now, that it's not as easy as that but I'm thinking there MUST be a way - even if it requires creating a new SProc.
TIA
Delete: Parameters From Another Table
Sep 27, 2006DELETE FROM StoresViewToTable2
WHERE (AllZips = store1.AllZips) AND (StoreNo = store1.StoreNo)
I want to delete from StoresViewToTable2 where the fields match in another table (Store1). How do you get the where parameters to be from another table? is this possible? or do i just need to do an Outer Join. Im trying to avoid this becuase the StoresViewToTable2 tbl is very large.
StoresViewToTable2 (tbl)
AllZips | StoreNo
Store1 (tbl)
AllZips | StoreNo
Table Parameters In Stored Procedures
Dec 9, 2007How can I pass a name of a table to a stored procedure.
I want to pass the name as a parameter. The table already exists in the db.
After that, I will do
"SELECT .....
FROM @tableparameter"
What is the right way to do that?
DB Engine :: Streaming And Table Valued Parameters
Jul 11, 2015I want to insert lots of data into two tables. For this I want to use table valued parameters and a stored procedure. So, what is the better way for best performance? Using two stored procedures or a single procedure with two parameters? How does SQL Server consume the data if I use a single procedure with two parameters. Is it really streaming the data? I mean, is SQL Server already starting to insert the first rows as soon as it gets it even if the client is still sending more and more rows and than the same with the next table?
e.g. I have a procedure like this:
CREATE PROCEDURE [dbo].[usp_UpdateElements] (
@tvpElementsToInsert As [dbo].[tvpElements] Readonly,
@tvpElementValuesToInsert As [dbo].[tvpElementValues] Readonly) AS
Begin
Insert Into Elements Select * From @tvpElementsToInsert;
Insert Into ElementValues Select * From @tvpElementValuesToInsert;
End
Giving A Proc Database And Table Names As Parameters
Jan 10, 2007Hello!Is it possible to pass a stored procedure a parameter, say @table anduse it as a table in the sql command?Finally i want a proc to copy tables from a database to another database.THERE IS MY CODE:CREATE PROCEDURE [user].[copytable]@dbSRC varchar(100),@dbTRGT varchar(100),@table varchar(100)ASBEGIN TRANSACTION FreeAndCopyTableTRUNCATE TABLE [@dbTRGT].admin.[@table]INSERT INTO [@dbTRGT].admin.[@table]SELECT *FROM [@dbSRC].admin.[@table]COMMIT TRANSACTION FreeAndCopyTableIF @@error <0 GOTO E_Copy_FailE_Copy_Fail:ROLLBACK TRANSACTION FreeAndCopyTableGOthanks in advance,muh
View 2 Replies View RelatedHow To Put A Table And A Chart In The Same Page In A Report And Let Them Have The Same Parameters Passed To Each Other?
Apr 13, 2007
Hi, all experts here,
Thank you very much for your kind attention.
I am having a question about how can I put a chart and a table in a same page of my report? And let the same parameters passed between them? Is it possible in SQL server 2005 Reporting Services? Please would any experts here shed me any light on it?
Thank you very much in advance and I am looking forward to hearing from you.
With best regards,
Yours sincerely,
SQL Server 2012 :: Passing Parameters To Table Valued Function?
Mar 13, 2014I am creating a function where I want to pass it parameters and then use those parameters in a select statement. When I do that it selects the variable name as a literal not a column. How do I switch that context.
Query:
ALTER FUNCTION [dbo].[ufn_Banner_Orion_Employee_Comparison_parser_v2]
(
@BANNER_COLUMN AS VARCHAR(MAX),
@ORION_COLUMN AS VARCHAR(MAX)
)
RETURNS @Banner_Orion_Employee_Comparison TABLE
[code]....
Returns:
I execute this:
select * from ufn_Banner_Orion_Employee_Comparison_parser_v2 ('a.BANNER_RANK' , 'b.[rank]')
and get:
CerecerezNULLNULLa.BANNER_RANKb.[rank]
Creating Stored Procedure With Temp Table - Pass 6 Parameters
Sep 9, 2014I need to create a Stored Procedure in SQL server which passes 6 input parameters Eg:
ALTER procedure [dbo].[sp_extract_Missing_Price]
@DisplayStart datetime,
@yearStart datetime,
@quarterStart datetime,
@monthStart datetime,
@index int
as
Once I declare the attributes I need to create a Temp table and update the data in it. Creating temp table
Once I have created the Temp table following query I need to run
SELECT date FROM #tempTable
WHERE #temp.date NOT IN (SELECT date FROM mytable WHERE mytable.date IN (list-of-input-attributes) and index = @index)
The above query might return null result or a date .
In case null return output as "DataNotMissing"
In case not null return date and string as "Datamissing"
How To Perform SELECT Query With Multiple Parameters In A Single Field In A Table
Sep 13, 2006i just can't find a way to perform this Select Query in my ASP.Net page. I just want to find out the sales for a certain period[startDate - endDate] for each Region that will be selected in the checkbox. Table Sales Fields: SalesID | RegionID | Date | Amount This is how the interface looks like.Thank You.
View 1 Replies View RelatedSQL Server 2012 :: How To Insert One To Many Valued Parameters From A Stored Procedure Into A Table
Jan 14, 2015I have a SP with Parameters as:
R_ID
P1
P2
P3
P4
P5
I need to insert into a table as below:
R_ID P1
R_ID P2
R_ID P3
R_ID P4
R_ID P5
How can I get this?
T-SQL (SS2K8) :: Passing Multiple Parameters With Table Valued Parameter To Stored Procedure?
May 21, 2014Can we Pass table valued parameters and normal params like integer,varchar etc..to a single stored procedure?
View 1 Replies View RelatedADO.NET 2-VB 2005 Express Form1:Printing Output Of Returned Data/Parameters From The Parameters Collection Of A Stored Procedure
Mar 12, 2008Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Thanks in advance,
Scott Chang
Run Report By Different Parameters Without Having To Enter Information For All Parameters At Same Time
Oct 29, 2013I have a SSRS report with four parameters,and I want to be able to enter information for two of the parameters and run the report opposed to all four of them. However, when I select allow blanks and only select the parameters that I want to run the report by, the report come back blank..Essentially, I want to be able to the run report by different parameters without having to enter information for all parameters at the same time.
View 2 Replies View RelatedQuery Duration Using Parameters Vrs No Parameters
Apr 27, 2006Hi,
I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.
I open a SQLProfiler and this is what I have :
exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @CustomerId',
N'@CustomerId nvarchar(4000)', @CustomerId = N'11111
I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)
I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.
Here it comes the first question...
Why does using parameters takes way too much time more than not using parameters?
Then, I decided to move the query to a Stored Procedure and it executes in a snap too.
The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP
I usually do it something like
(@CustomerId is null or CustomerId = @CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.
Is there a way to handle "dynamic parameters" in a efficient way???
Parameters Dependencies When Using Parameters!Name.Label
Mar 7, 2007Hello:
I just recently bumped into this problem and I think I know what's causing it. This is the setup:
Report Parameters: FromDate, ToDate, DivisionalOffice, Manager, SalesRep
dsCalendarEvents Parameters: FromDate.Value, ToDate.Value, DivisionalOffice.Value,
dsDivisions Parameters: N/A
dsManager Parameters: DivisionalOffice.Value
dsSalesRep: DivisionalOffice.Label
When I query the ReportServices WS and scan the parameter dependencies for SalesRep it says there are four dependencies: FromDate, ToDate, DivisionalOffice and Manager!!!
If I change "dsSalesRep" to use "DivisionalOffice.Value" the ReportingServices WS parameter dependency scan returns only one dependency for "SalesRep" parameter!!!( This is the correct behavior )
Has anybody seen this behavior and more importantly, is there a work around?
Regards,
Parameters.AddWithValue Vs. Parameters.Add
Jan 2, 2008Hello all,
Given:
string commandText = "Categories_Delete";SqlCommand myCommand = new SqlCommand(commandText, connection);myCommand.CommandType = CommandType.StoredProcedure;
Is there a reason NOT to use myCommand.Parameters.AddWithValue("@CategoryID",CategoryID); I'd prefer to use that over myCommand.Parameters.Add("@CategoryID", SqlDbType.Int, 4).Value = CategoryID; as I have these functions being created dynamically and hope to get away from a big lookup to try to convert System.Types into SqlDbTypes. [shudder]
It seems that ADO.NET makes an implicit conversion to the valid type. If this is correct then I can move on fat dumb and happy. Anyone have any good insight?
Thanks,
Uses Of Parameters
Sep 2, 2007HI
Can someone explain me in detail when we use <asp:parameter> and when we use <asp:controlparameter>
SQL Parameters
Sep 6, 2007Hello Dears
I Have an stored procedure like this :ALTER proc [dbo].[gl_voucher_type_insert]
@company_code varchar(3),@source_code varchar(4)
,@voucher_code varchar(4),@desc_a nvarchar(50)
,@desc_l nvarchar(50)
,@voucher_form numeric(2) ,@voucher_prefix varchar(4)
,@voucher_start numeric(8)=null
asDeclare @PrefixCount as int
set @PrefixCount=isnull((select count(*) from gl_voucher_type
where company_code=@company_code and
voucher_code=@voucher_code),0)
if @PrefixCount=0
begin
insert into gl_voucher_type(company_code,source_code,voucher_code,voucher_desc_a,voucher_desc_l,voucher_form,voucher_prefix,voucher_start)
values(@company_code,@source_code,@voucher_code,@desc_a,@desc_l,@voucher_form,@voucher_prefix,@voucher_start)
end
return @PrefixCount
ok i need in my asp page get the @ prefixCount value to make some checking on it how can i do that
please help me as soon as possible
with my best regard
khalil T.Hamad
Parameters In SP
Sep 27, 2007CREATE PROC xxx
@user VARCHAR(15),
@rank varCHAR(10) AS
DECLARE @sql VARCHAR(100)
SET @sql = 'SELECT ' + @user + ' FROM usertable where grade = ' + @rank
EXEC (@sql)
GO
when i execute this proc without where condintion its working, but when i use where condition its dispalyin invalid column name with the name im passing
eg.
xxx admin,aB
WHEN I TRY TO EXECUTE PROC WITH ABOVE STAT, ITS DIAPLAYIN ERROR AS "INVALID COLUMN NAME ab
but
xxx admin," ' aB ' "
when i try like this its giving result.
how can i avoid second method of executin the proc and use first method for the sake of passing value from frontend
Add Parameters
Jun 21, 2008Hi,
The following code doesnt work. I am trying to get data from a
table according to a querystring. Id like the data in the columns
'hello' and 'hello2' to be meta name and content. But it says
The name 'hello' does not exist in the current context
command.CommandText =
"SELECT hello, hello2 FROM table WHERE ID=@ID";
command.Parameters.AddWithValue("@ID",
Request.QueryString["ID"]);
command.ExecuteNonQuery();
HtmlMeta meta = new HtmlMeta();
meta.Name = "Description";
meta.Content = "first" + hello;
Page.Title = "first" + hello2;
Header.Controls.Add(meta);
Thanks
Add Parameters
Jan 14, 2004I need to add parameters to my SQL string, like Where [EndDate] >= @HStart AND [EndDate] <= @HEnd, I tried to Dim variables but it caused an error. Can anyone help me with this?
Thank You,
Sub BindDataCurrent()
Where [EndDate] >= @HStart AND [EndDate] <= @HEnd"
'MyCommand.Parameters.Add("@HStart", SqlDbType.VarChar, 80).Value = HistoryStartText.Text
'MyCommand.Parameters.Add("@HEnd", SqlDbType.VarChar, 80).Value = HistoryEndText.Text
ConnectStr = ConfigurationSettings.AppSettings("ConnectStr")
Dim MyConnection As SqlConnection = New SqlConnection(ConnectStr)
MyConnection = New SqlConnection(ConnectStr)
Dim SQL As String = "Select [Campaign_ID], [Campaign Type], [Campaign Date], [EndDate],[Comment] FROM tblCampaignTracking Where [EndDate] >= @HStart AND [EndDate] <= @HEnd"
Dim DA As SqlDataAdapter = New SqlDataAdapter(SQL, MyConnection)
Dim DS As New DataSet
DA.Fill(DS, "tblCampaigns")
MyEditDataGridCurrent.DataSource = DS.Tables("tblCampaigns").DefaultView
MyEditDataGridCurrent.DataBind()
End Sub
Sql Parameters
Nov 1, 2004Hi,
I'm having a bit of trouble with SQL Parameters. I can't seem to define the type when creating the parameters.
Here's what I've done:
I have a function that processes my request and returns a datatable:
Public Function Grab_Data(querystring asn string, params() as SqlParameter) as DataTable
... dims all the necessary variables
Try
...creates the connection and command
Dim p as SqlParameter
For each p in params
p = command.parameters.add(p)
p.direction = parameterdirection.input
Next
...opens connection, creates dataset and fills it
Finall
....disposes connection and command
End Try
Return datatable
End Function
I call this function by:
...dim the necessary variables
querystring = "SELECT * FROM Tbl_Users WHERE Joined>=@date"
dataTable = Grab_Data(querystring, New SQLParameter("@date", DateTime.Today))
My code works fine and I get the results that I want, but no where in my code is the type of the parameter defined.
I tried calling the function like:
Grab_Data(querystring, New SQLParameter("@date", SQLDBType.DateTime, DateTime.Today))
But when I do this, I get an error saying that no value is assigned to @date.
Can anyone tell me what I need to modify so that I can pass the type of the parameter to the function?
The reason why I am not explicity defining the parameters in the function is because I can reusing the function numerous times throughout my code. So some calls have three parameters passed to it and some and none.
Thanks.
Sql Parameters
Dec 18, 2001Hi,
I'm new in sql. How can i set the current value of parameter "MAX DEGREE OF PARALLELISM" to new value?
Thank you
Rey
Need Help With DTS Parameters
Oct 27, 2001Hi,
I'm trying to create a DTS transform (sql2k) with a paramterized query like:
SELECT columns
FROM table
WHERE column1 IN ?
When I try to preview this I get an error "no value given for one or more required parameters". The global variable is set and I can see the value.
Any ideas? I've been struggling with this for days with no success.
Thanks,
SQL - Using Parameters
Dec 3, 2005Hi all, I know I could do something like this in SQL:
select customers.name
from customers
where customers.name = request.form("txtname")
but my question is, can I have a user pick the operator (such as =,>,<,>=,<=) from a dropdown box and pass it the the sql statement as a parameter such as:
select customers.name
from customers
where customers.name request.form("txtoperator") request.form("txtname")
Please help!
Thanks!
Parameters
Apr 11, 2008Hi all, I am using classic ASP and SQL Server 2005. Can I use parameters like @whatever in my asp code or are they for stored procedures exclusively? I am trying to change the output of a request.form into a paramater so I can prevent SQL injection I am aware of using trim to counter this but I wasn't sure of the best practice.
I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.
Parameters
Apr 24, 2008I have a set of date parameters in reporting services which are defaulted to 3/01/08. How can I make them to the current month so that six months from now they are not still reading 3/01/08
View 5 Replies View RelatedParameters And LIKE
Apr 17, 2006I'm trying to do this:
ALTER PROCEDURE dbo.SkillSearch
(
@skillname char(255)
)
AS
SET NOCOUNT ON
SELECT * FROM Skill WHERE SkillName LIKE @skillname
However, when I run dbo.skillsearch 'ph%'
I get an empty set, while dbo.skillsearch 'php' returns the results expected.
I am therefore assuming that I can't use a parameter for a LIKE clause with any wildcards?
Is there any way around this other than manually building the SQL statement in the SP and then executing it? I'd obviously prefer to not have to do it that way for all the SQL Injections and related reasons.
Wouldn't this also kill my query optimization benefits, manually building the statement each time?
Thanks,
- Brian
Parameters Through URL
Jan 24, 2007I am trying to use the URL to pass a parameter to a report. I have tried several ways to make this happen with no effect. My report is using a sproc that has a int parameter. I have tried make a list of partial parameters and passing the parameter I want through the URL. I hide the prompt. I put NULLs and Blanks allowed. I have created a linked report then hid the prompt. I used parameters=false instruction. Here is the basic URL http://reportsrv/Reports/Pages/Report.aspx?ItemPath=%2fActiveTasks%2fProject+Details&rs:Command=Render&ProjectID=56.
Please Help it is driving me crazy.
Help In Parameters
Nov 5, 2007hi i need to know what's the syntax in reporting services query in a where statement to get values where comment card name like '%'&@RestaurantName&'%'. This syntax is giving me an error. i need to get the rows where the comment card name contains the restaurant name got from the parameter. can anyone give me the right syntax please?
View 1 Replies View Related