I need to write a CLR which returns the column names and if possible datatype which are outputted from a stored procedure.

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?

February 7th, 2011 7:26pm

Hello,

Why that complicated like a CLR? Simply use the SET FMTONLY command to get the result definition:

SET FMTONLY ON; 

EXEC sp_help;

 

Free Windows Admin Tool Kit Click here and download it now
February 7th, 2011 9:32pm

I am using the SQL TDD Framework DbFIT which requires column names as a dataset.
February 8th, 2011 2:21pm

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>

<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
</article><footer style="margin:20px 40px;min-height:20px;padding:19px;background-color:#f5f5f5;border:1px solid rgb(227, 227, 227);border-top-left-radius:4px;border-top-right-radius:4px;border-bottom-right-radius:4px;border-bottom-left-radius:4px;-webkit-box-shadow:rgba(0, 0, 0, 0.0470588) 0px 1px 1px inset;box-shadow:rgba(0, 0, 0, 0.0470588) 0px 1px 1px inset;color:#333333;font-family:'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13.63636302947998px;line-height:20px;"></footer>

Free Windows Admin Tool Kit Click here and download it now
July 31st, 2013 6:06am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics