I need to write a CLR which returns the column names and if possible datatype which are outputted from a stored procedure.
I do not need to collect any of the data which the stored procedure procedures?
Is this possible?
Technology Tips and News
I need to write a CLR which returns the column names and if possible datatype which are outputted from a stored procedure.
I do not need to collect any of the data which the stored procedure procedures?
Is this possible?
Hello,
Why that complicated like a CLR? Simply use the SET FMTONLY command to get the result definition:
SET FMTONLY ON;
EXEC sp_help;
Into dbFit you shoud try this :
First of all a dynamicaly create a temporary Table #StoreProcedureResult from au dedicated UDF same expected format as storeprocedure
Execute | SELECT TOP 0 * INTO #StoreProcedureResult FROM FT_QKV_Cmd_GMWwithParameters('20130227','20130228') |
Execute | INSERT INTO #StoreProcedureResult EXEC dbo.P_QKV_Cmd_GM '20130227','20130228' |
Store Query | SELECT * FROM #StoreProcedureResult ORDER BY 1 | SP |
Store Query | SELECT * FROM FT_QKV_Cmd_GMWwithParameters('20130227','20130228') ORDER BY 1 | UDF |
Get Table Description Helper to write the test
<article style="margin:0px 40px;color:#333333;font-family:'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13.63636302947998px;line-height:20px;">
Query | Select @MyColumnList =ISNULL(@MyColumnList,'|') + name+'|' FROM sys.columns WHERE OBJECT_ID = OBJECT_ID('FT_QKV_Cmd_withParameters'); SELECT @MyColumnList as cList |
cList? | |
>>ColumnList |
<article style="margin:0px 40px;color:#333333;font-family:'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13.63636302947998px;line-height:20px;">Then Compare the results</article>
<article style="margin:0px 40px;color:#333333;font-family:'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13.63636302947998px;line-height:20px;">
compare stored queries | SP | UDF | ||||||||||||||||||||||||||||||||||||||||||||||||||||
Cmd_Code | Cmd_Origine | Cmd_NumP | Cmd_Num | Cmd_Type | date_commande_e | date_commande | date_commande_hh | date_commande_mm | date_paiement | Cmd_PaiExp | Cli_Code | Cmd_Lid | Livraison_ville | Livraison_CodePays | Livraison_codepostal | Facturation_email | Facturation_prenom | Facturation_nom | Facturation_ville | Facturation_codepostal | Facturation_CodePays | Cmd_TrspDemande | Cmd_TotalTTC | Cmd_AdresseIP | Cmd_Blocage_Code | Cmd_Blocage_Info | Cmd_BlocageDate | Cmd_Statut | Cmd_DateExpeAnnoncee | Cmd_PK_PaysISO | Cmd_PK_Id | sysMod_date | lc_TypoPanier | moyen_paiement | Facturation_Departement | Facturation_Region | Livraison_Departement | Livraison_Region | YaAbotel[?] | YaPaiement3fois[?] | YaBundle[?] | YaContratOr[?] | cmd_IPagent | cmd_nomAgent | FlagChat[?] | FlagTel[?] | FlagWeb[?] | TypoCommande[?] | LibelleTypoCommande[?] | Cmd_TotalTTC_old | OrigineDevice[?] | AConsidererPRO | FlagMonoLigne[?] | FlagMonoQte[?] |
Execute | DROP TABLE #StoreProcedureResult |